Link to last non-blank cell in another worksheet
 

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

Link to last non-blank cell in another worksheet
 

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

Link to last non-blank cell in another worksheet
 

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

Link to last non-blank cell in another worksheet
 

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

Link to last non-blank cell in another worksheet
 

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