References last cell in a continually lengthening column.

[Jeff Brown]

For example: In Column A, I add a new entry every day,
leaving the previous ones in place. In column B, I have a
cell with a formula that must use the most recent entry in
column A.

How can I get the formula to automatically find the last
cell in Column A containing data? I don't want to go into
the Column B cell to manually change the reference every
day.

 

[Norman Harker]

Hi!

And in all probability stealing from one of Aladin's previous posts:

If column A data can be values or text or Boolean TRUE or FALSE then
the following array entered formula will return the last entry of any
type in column A.



=INDIRECT("A"&MAX(IF(NOT(ISBLANK(A1:A65535)),ROW(1:65535))))

Entered as an array by pressing and holding down Ctrl + Shift and then
pressing Enter, When correctly entered this appears in the cell as:



{=INDIRECT("A"&MAX(IF(NOT(ISBLANK(A1:F65535)),ROW(1:65535))))}



Note that as per standard definition an empty string "" returned by a
formula is regarded as text.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Tuesday 5th August 2003: Burkina Faso
(Independence Day), Croatia (Homeland Thanksgiving Day), Dominican
Republic (Santo Domingo Foundation Day), El Salvador (Transfiguration
San Salvador del Mundo), Peru (Virgen de las Nieves), Tuvalu (National
Children’s Day). Observances: Hiroshima Day (23:15 UST 1945)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.