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

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)


"Shelly" wrote:

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

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

On Thu, 19 Apr 2007 07:58:07 -0700, Shelly
wrote:

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

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

"JLatham" wrote:

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)


"Shelly" wrote:

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

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

"Gord Dibben" wrote:

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

On Thu, 19 Apr 2007 07:58:07 -0700, Shelly
wrote:

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