From: "Stuart MacDonald" <stua...@connecttech.com>
Subject: OFFSET oddity
Date: Thu, 5 Oct 2006 10:59:59 -0400
Lines: 67
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
Message-ID: <uIPC78I6GHA.3452@TK2MSFTNGP05.phx.gbl>
Newsgroups: microsoft.public.excel.worksheet.functions
NNTP-Posting-Host: inetc.connecttech.com 64.7.140.42
Path: g2news2.google.com!news2.google.com!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.freenet.de!news.germany.com!news.banetele.no!uio.no!newsfeed.cw.net!cw.net!news-FFM2.ecrc.de!TK2MSFTFEEDS01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl

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