OFFSET troubles...
Posted by Todd Kasenberg on August 14, 2001 6:16 AM
I have named a dynamic range using offset...here is the formula:
=offset($l$1,0,0,29,count($o$4:$e$z4)). My obvious goal is to select an area from L1 that extends 29 rows deeps, and that could extend as far as ez4, but I want the range to end with the last cell that has a number.
So far, so good.
However, after accepting this for a named range, Excel automatically puts the sheet reference in, so it looks like this:
=offset(sheet1!$l$1,0,0,29,sheet1!$o$4:$ez$4)).
This is counterproductive to me...I want the page to be relative, in other words, i want the dynamic range to refer to the current page, and not the fixed page which was active when I defined the name.
Anyone see a solution to my impasse?
Posted by Mark W. on August 14, 2001 6:27 AM
=OFFSET(INDIRECT("l1"),0,0,29,COUNT(INDIRECT("O4:EZ4")))
Posted by Todd Kasenberg on August 14, 2001 12:14 PM
Thanks for trying, Mark, but it doesn't work - it does some weird stuff though (creates a new name in the list, very odd)
Posted by Mark W. on August 14, 2001 1:15 PM
Yes, it does! Are you doing something weird with
VBA? Before you do something with VBA just choose
Excel's Insert | Name | Define... menu command
and enter "test" in the "Names..." field and...
=OFFSET(INDIRECT("L1"),,,29,MAX(1,COUNT(INDIRECT("O4:EZ4"))))
...in the "Refers to" field. And then press [ OK ].
Note: I added the MAX() function as an enhancement
in the event that O4:EZ4 are blank.
Once you've defined "test" go to any worksheet,
type Control+G, enter "test" in the "Reference"
field, and press [ OK ].
It works!!! Now if you're implementing this in
VBA you may need to consult some of the VB-ers
on this board.
Posted by Todd Kasenberg on August 15, 2001 5:48 AM
Mark: It works! (I'm not sure I understand why, but hey? does understanding matter...lol) It is amazing! Thank you so much for this solution - this problem has bothered me for 3 weeks now!
