"GS" wrote in message
...
Rick Rothstein was thinking very hard :
Rick, this doesn't return the expected value (as per my understanding
of OP's scenario). Keeping in mind that the expected value is always
'relative' to the first blank row below the newly inserted data. This
means as rows are inserted the previous data is now above the new
row.

Possibly, I misunderstand what the OP wants...

Sorry, it works if I fill all cells above the blank rows. Might be an
issue, though, if that condition doesn't persist!

I tried to clarify the conditions under which the formula would work in
the opening sentence. One of the biggest problems I find in answering
questions online is that those asking the question assume we don't need
to know all the details about their setup and how the formula will be
used... makes us all have to guess our way to a solution for them.

Rick Rothstein (MVP - Excel)

True enough! Ugh...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


Thanks Garry and Rick,
Sorry for not explaining all conditions( i didn't know the possibility of
empty rows above
the area I was looking at would matter)
Garry's method of Offset appears to work for my needs as it doesn't depend
on
the conditions at the top of the column (which have some headers and summary
stuff
and possible blank cells)
It appears I don't even need to name the cell, as =OFFSET(C[x],-4,0) works
perfect.
where [x] is the current row with the lookup function, and -4 takes care of
the two
blank rows above and the one last entered data row above that to get to the
penultimate
data row.
Thanks again for everyone's ideas and help
mark