return the bottom value in a column

A

anthonyberet

I have a worksheet which is updated regularly with a new line, working
from the top down. I would like to find worksheet function which can
return the value in the bottom cell of a column which is not blank, so
that this value can be used in another worksheet.

ie I want to be able to return the most recent value in a column at any
time.

I had hoped there would be a ready-made function for this, but it
appears not.

Any ideas?
 
G

Gord Dibben

=LOOKUP(9.99999999999999E+307,F:F) willl fetch the last numeric value in F

=LOOKUP(REPT("z",255),B2:B10000) If column B data is of 'text' type, use this

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535) will fetch last value, number or text


Gord Dibben MS Excel MVP
 
A

AD108

Hi Gord,

Would you be willing to explain the last one of these three you offered.

Thanks

AD108

Gord Dibben said:
=LOOKUP(9.99999999999999E+307,F:F) willl fetch the last numeric value in F

=LOOKUP(REPT("z",255),B2:B10000) If column B data is of 'text' type, use this

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535) will fetch last value, number or text


Gord Dibben MS Excel MVP
Click to expand...
 
G

Gord Dibben

See Bob Phillips' site for explanations of all the functions that can be used to
find last value.

Bob and the late Frank Kabel compiled an extensive list......one of which is the
function in question. Scroll down 2/3 page to find it.

http://www.xldynamic.com/source/xld.LastValue.html

They do a much better job than I could ever hope for.


Gord


Hi Gord,

Would you be willing to explain the last one of these three you offered.

Thanks

AD108

Gord Dibben said:
=LOOKUP(9.99999999999999E+307,F:F) willl fetch the last numeric value in F

=LOOKUP(REPT("z",255),B2:B10000) If column B data is of 'text' type, use this

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535) will fetch last value, number or text


Gord Dibben MS Excel MVP
Click to expand...

Gord Dibben MS Excel MVP
Click to expand...
 
A

anthonyberet

Gord said:
See Bob Phillips' site for explanations of all the functions that can be used to
find last value.

Bob and the late Frank Kabel compiled an extensive list......one of which is the
function in question. Scroll down 2/3 page to find it.

http://www.xldynamic.com/source/xld.LastValue.html

They do a much better job than I could ever hope for.
Click to expand...
Thank you very much. It's odd that M$ haven't seen the need for a
worksheet function to do this.
 
Top