making a cell equal the last entry on a sheet

[drag-driver]

Problem: Want a cell in a sheet to show the last entry on another sheet in a given colum.
I.e as an entry is added to the colum in one sheet i want the cell in the other sheet to show that entry and not the previous one ie show the latest entry every time it is added to the colum.

[Aladin Akyurek]

On 2002-05-15 08:04, drag-driver wrote:
Problem: Want a cell in a sheet to show the last entry on another sheet in a given colum.
I.e as an entry is added to the colum in one sheet i want the cell in the other sheet to show that entry and not the previous one ie show the latest entry every time it is added to the colum.

Suppose you want A1 in sheet1 to show the last entry in column B in Sheet2.

If B in sheet2 is numeric, use in A1 in sheet1 one of:

=OFFSET(Sheet2!B1,MATCH(9.99999999999999E+307,Sheet2!B:B)-1,0,1,1)

=INDEX(Sheet2!B:B,MATCH(9.99999999999999E+307,Sheet2!B:B))

aladin

[Brian]

I was intrigued, so I tried it.

{Jump to the last line for universal code using an array formula!}

The suggested code works for numbers, but does not work for letters.

Using something like "ZZZZZZZZZZZZZZZZZZZ" works for text but not numbers, and you would need to use 32,000 Zs to cover the largest possible string.

False & Ture are don't work.

A VBA function would work, but not desired .

Or using the maximum of the results:

=INDEX(Sheet2!B:B,MAX(MATCH(9.99999999999999E+307,Sheet2!B:B,1),MATCH("ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ",Sheet2!B:B,1),IF(ISERROR(MATCH(FALSE,Sheet2!B:B,1)),1,MATCH(FALSE,Sheet2!B:B,1)),IF(ISERROR(MATCH(FALSE,Sheet2!B:B,1)), 1,MATCH(TRUE,Sheet2!B:B,1))))

but only finds the first TRUE or FALSE.

So ... I tried array Formulas and it worked for all possible values, even errors (ex #N/A!) !

=MAX(IF(NOT(ISBLANK($B$1:$B$65535)),ROW($B$1:$B$65535),0))

But for some reason, this will not accept a full column. If that is a probem, then use:

=IF(NOT(ISBLANK($B65536)),65536,MAX(IF(NOT(ISBLANK($B$1:$B$65535)),ROW($B$1:$B$65535),0)))

Remember to use CTRL-SHIFT-ENTER to create the array formula.

Thanks for the practice.

[Brian]

Woops, you need to replace the MATCH statement to get the value (I just found the last row).

ie =INDEX(Sheet2!B:B,IF(NOT(ISBLANK($B65536)),65536,MAX(IF(NOT(ISBLANK($B$1:$B$65535)),ROW($B$1:$B$65535),0))))

works