Link to last non-blank cell in another worksheet

[Guest]

Worksheet 1 contains weekly data, for example cells A1 thru A4 contain dates,
the other cells are still blank :

1/3/07
1/10/07
1/17/07
1/24/07
<blank cell>
<blank cell>

What formula can I enter in a worksheet 2 cell to display the last date in
the list?

Thank you in advance for your help.

Shelly

 

[Guest]

If the dates are always increasing down the sheet (no chance of 1/3/07 ending
up at the bottom of that list) then
=MAX('Sheet1'!A:A) would do the trick, or if you have a good idea of the
maximum number of entries that may end up in column A on sheet 1, you can
limit it with something like (assuming you know there'll never be more than
366 dates entered)
=MAX('Sheet1'!A1:A366)

 

[Gord Dibben]

Shelly

If data is true dates.................

=LOOKUP(10^10,Sheet1!A:A) entered in a cell on Sheet2

If text dates try this one..................

=LOOKUP(2,1/(Sheet1!A1:A65535<>""),Sheet1!A1:A65535)

which will fetch the last value regardless of type.

Note that you can't use A:A as a range in second formula.


Gord Dibben MS Excel MVP

 

[Guest]

Thank you - it's exactly what I need. Thanks again!

 

[Guest]

Thank you, too, Gord. This will come in very handy when the data type differs.