Cell reference

[projectile]

is this possible

grab current cell reference from sheet 2 , subtract 18 rows + 7 columns

this will when provide reference to sheet 1 which contains the value

thanks

 

[CharlesChuckieCharles]

Try

=IF(ISNA(VLOOKUP(OFFSET(I32,-18,-7),Sheet1!$A:$W,2,FALSE)),"",VLOOKUP(OFFSET(I32,-18,-7),Sheet1!$A:$W,2,FALSE))

where I32 is the cell reference on sheet2 where you stick this formula

 

[jasonb75]

Maybe

=indirect("Sheet1!R[-18]C[7]",0)

 

[projectile]

Hi

Thanks for the formula

it is returning #REF!

here is the formula , pasted into sheet named IM WEEK , cell C4034

=IF(ISNA(VLOOKUP(OFFSET(C4034,-18,-7),'NVO WEEK'!$A:$W,2,FALSE)),"",VLOOKUP(OFFSET(C4034,-18,-7),'NVO WEEK'!$A:$W,2,FALSE))



Is it complaining because the offset is looking at itself?

 

[CharlesChuckieCharles]

Does
=OFFSET(C4034,-18,-7)
return a value

can that value be found in
'NVO WEEK'!$A

since vlookup uses the offset value to compare against the first column in the other sheet an returns the value in column 2 (in this example)

 

[jasonb75]

Does
=OFFSET(C4034,-18,-7)
return a value

That will return #REF!, you're referencing row 4016, column -4, the lowest valed numeric column reference is 1 (column A)

 

[CharlesChuckieCharles]

Does subtract 18 rows + 7 columns

mean
subtract 18 rows and 7 Columns
OR
subtract 18 rows and add 7 columns
?

 

[projectile]

Does subtract 18 rows + 7 columns

mean
subtract 18 rows and 7 Columns
OR
subtract 18 rows and add 7 columns
?

Hi

it means
subtract 18 rows and add 7 columns

 

[jasonb75]

Does subtract 18 rows + 7 columns

mean
subtract 18 rows and 7 Columns
OR
subtract 18 rows and add 7 columns
?

Also, assuming that the last variation above is the correct one, should the formula in C4034 simply return the value that is in J4016 of the other sheet?

If that's the case and you just want a relative formula to fill down then all you need is =Sheet1!J4016