return the bottom value in a column
 

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?

return the bottom value in a column
 

=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


On Mon, 27 Nov 2006 00:42:05 +0000, anthonyberet wrote:

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?

return the bottom value in a column
 

Hi Gord,

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

Thanks

AD108

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
=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


On Mon, 27 Nov 2006 00:42:05 +0000, anthonyberet
wrote:

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?

return the bottom value in a column
 

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


On Sun, 26 Nov 2006 17:18:19 -1000, "AD108"
wrote:

Hi Gord,

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

Thanks

AD108

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
=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


On Mon, 27 Nov 2006 00:42:05 +0000, anthonyberet
wrote:

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?



Gord Dibben MS Excel MVP

return the bottom value in a column
 

Gord Dibben wrote:
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.


Thank you very much. It's odd that M$ haven't seen the need for a
worksheet function to do this.