Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
offset not working
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? -- Boris |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
offset not working
Hi
"BorisS" wrote in message ... 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? 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) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
offset not working
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. -- Boris "Arvi Laanemets" wrote: Hi "BorisS" wrote in message ... 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? 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) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
amount of working days per month | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Question for use of offset and range | Excel Worksheet Functions | |||
Working time and days | Excel Discussion (Misc queries) |