CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formulagoogle-sheets-query
Jacob Bayer
Jacob Bayer

Reputation: 93

Call many cells from another sheet in Google Sheets

I have a very complicated formula:

=if(
and(A$1>$W4, A$1<=$X4, $X4>B$1),
$U4, 
if(and($W4>=A$1,$W4<B$1),
days(B$1,$W4)*($U4/days(B$1,A$1))+0, 
if(and($X4>A$1,$X4<=B$1),
days($X4,A$1)*($U4/days(B$1,A$1)),
0)))

And rather than type 'Other Sheet'!cell for every cell reference, I would like to know if there's a way I can describe the entire sheet prior to referencing cells from that sheet. I thought that perhaps this would work but it obviously doesn't. Any solutions?

=query('othersheet'!cell:cell,if(
and(A$1>$W4, A$1<=$X4, $X4>B$1),
$U4, 
if(and($W4>=A$1,$W4<B$1),
days(B$1,$W4)*($U4/days(B$1,A$1))+0, 
if(and($X4>A$1,$X4<=B$1),
days($X4,A$1)*($U4/days(B$1,A$1)),
0))))

Upvotes: 0

Views: 31

Answers (1)

player0
player0

Reputation: 1

unfortunately no, you still need to reference it

=IF(AND('othersheet'!A$1 >  'othersheet'!$W4, 
        'othersheet'!A$1 <= 'othersheet'!$X4, 
        'othersheet'!$X4 >  'othersheet'!B$1), 'othersheet'!$U4, 
 IF(AND('othersheet'!$W4 >= 'othersheet'!A$1,
        'othersheet'!$W4 <  'othersheet'!B$1),
 DAYS('othersheet'!B$1, 'othersheet'!$W4)*('othersheet'!$U4/
 DAYS('othersheet'!B$1, 'othersheet'!A$1))+0, 
 IF(AND('othersheet'!$X4 >  'othersheet'!A$1,
        'othersheet'!$X4 <= 'othersheet'!B$1),
 DAYS('othersheet'!$X4, 'othersheet'!A$1)*('othersheet'!$U4/
 DAYS('othersheet'!B$1, 'othersheet'!A$1)), 0)))

or you can put your formula into some auxiliary column in your othersheet (let's say X4) and then just reference

='othersheet'!X4

Upvotes: 1

Related Questions