OFFSET oddity
LastInColumn() is a VBA function I got from the web for finding the
last value in a colummn. It works, and returns a reference to the cell
instead of the value.
I want to look up the last value in P, move over to L and look up the
last value before that. I've figured out how to do this correctly. My
question is about the operation of the OFFSET function.
Two different ways to get the same range, or so I thought:
(1) =LastInColumn(OFFSET(L$1,0,0,ROW(LastInColumn(P$1: P30)),1))
(2) =LastInColumn(L$1:OFFSET(LastInColumn(P$1:P30),0,-4,1,1))
I have these formulas in multiple worksheets. (1) always works
properly. The problem is (2) has some very odd behaviour. What happens
is on the active worksheet (2) works correctly, but on the non-active
worksheets (2) updates immediately (Is OFFSET volatile? Seems to be.)
and gets a range that includes cells from the active worksheet! This
then causes the formula to get incorrect data.
This happens whether I use my function inside the OFFSET() call, or
simply hardcode say P23. Debugging my function shows that some cells
in the range come from the wrong worksheet.
So it definitely seems to be a problem with the L$1:OFFSET() notation.
I think this should work, but it obviously doesn't. Why not?
Of course, I started out with (2) because it avoids one level of
nesting. I was able to use (1) when I found CHOOSE() which flattened
out the nested IF() problem.
....Stu
|