offset not working

B

BorisS

I have the following that does work:

=OFFSET('Revenue Assumptions'!D76,D24,C24)

I have the following which does not work:

=OFFSET(B24,D24,C24)

D and C 24 obviously stay the same.
My value for B24 is 'Revenue Assumptions'!D76

Any idea why the offset is not working when using for its reference point
the written name of a cell?
 
A

Arvi Laanemets

Hi


BorisS said:
I have the following that does work:

=OFFSET('Revenue Assumptions'!D76,D24,C24)

I have the following which does not work:

=OFFSET(B24,D24,C24)

D and C 24 obviously stay the same.
My value for B24 is 'Revenue Assumptions'!D76

Any idea why the offset is not working when using for its reference point
the written name of a cell?
Click to expand...

Because you try 'to display something, what is D24 rows down and C24 columns
left from cell B24. NB! From cell B24, not from cell addressed there. Offset
assumes, that first parameter is cell address, not a value.

Instead this you have to use
=OFFSET(INDIRECT(B24),D24,C24)
 
B

BorisS

you are right in logic, and I had tried this. It hadn't worked, but now I
realize why. My reference, when entered in the cell, is treated as a string
because of the ' that goes first (sheet reference). Since indirect() was
picking up only the part after the initial ', I had to write
indirect("'"&b24). Now it works. Just FYI. Thanks, though.
 
Top