Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup function/sum function | Excel Discussion (Misc queries) | |||
How do I combine spreadsheets and documents in one file? | Excel Discussion (Misc queries) | |||
Lookup values in one column to return another | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |