I want A1 to equal the contents of a cell in the "last row with contents".
If not, then I want the cell in the row above it, and if not I want the cell
in the row above that, and so on...

For instance...

A1=5
if...
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is 5
D6 is (empty)
D7 is (empty)
....

Second Example...

A1=4
if..
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is (empty)
D6 is (empty)
D7 is (empty)

In A1 enter the formula

=LOOKUP(10^10,D:D)
OR

=LOOKUP(10^10,D$1:D$100)


If this post helps click Yes
---------------
Jacob Skaria


"Rob" wrote:

I want A1 to equal the contents of a cell in the "last row with contents".
If not, then I want the cell in the row above it, and if not I want the cell
in the row above that, and so on...

For instance...

A1=5
if...
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is 5
D6 is (empty)
D7 is (empty)
...

Second Example...

A1=4
if..
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is (empty)
D6 is (empty)
D7 is (empty)

Try one of these...

=MAX(D1:D7)

=LOOKUP(1E100,D1:D7)

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I want A1 to equal the contents of a cell in the "last row with contents".
If not, then I want the cell in the row above it, and if not I want the
cell
in the row above that, and so on...

For instance...

A1=5
if...
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is 5
D6 is (empty)
D7 is (empty)
...

Second Example...

A1=4
if..
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is (empty)
D6 is (empty)
D7 is (empty)

On Tue, 13 Oct 2009 09:14:01 -0700, Rob wrote:

I want A1 to equal the contents of a cell in the "last row with contents".
If not, then I want the cell in the row above it, and if not I want the cell
in the row above that, and so on...

For instance...

A1=5
if...
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is 5
D6 is (empty)
D7 is (empty)
...

Second Example...

A1=4
if..
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is (empty)
D6 is (empty)
D7 is (empty)

If your values are all numbers, then:

A1: =LOOKUP(9.9E+307,D:D)

If there might be non-numeric data, then:

A1: =LOOKUP(2,1/(D:D<""),D:D)

Note that in versions of Excel prior to 2007, you may not be able to reference
an entire column. If that is the case, then substitute D1:D65535 for the D:D
references.
--ron

Ok, this =LOOKUP(10^10,D:D) worked excellent, but now I have a further
problem. I have to ultimately upload this spreadsheet to google docs for
others to look at, but google docs doesn't recognize this lookup formula.
Any more thoughts?

"Jacob Skaria" wrote:

In A1 enter the formula

=LOOKUP(10^10,D:D)
OR

=LOOKUP(10^10,D$1:D$100)


If this post helps click Yes
---------------
Jacob Skaria


"Rob" wrote:

I want A1 to equal the contents of a cell in the "last row with contents".
If not, then I want the cell in the row above it, and if not I want the cell
in the row above that, and so on...

For instance...

A1=5
if...
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is 5
D6 is (empty)
D7 is (empty)
...

Second Example...

A1=4
if..
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is (empty)
D6 is (empty)
D7 is (empty)

Thank you. I got some help over at Google Docs. The answer was ...
=ArrayFormula(FILTER( D:D ; ROW(D:D)=MAX(FILTER(ROW(D:D) ; ISNUMBER(D:D) ) )
))

Thank you everyone!

"Rob" wrote:

Ok, this =LOOKUP(10^10,D:D) worked excellent, but now I have a further
problem. I have to ultimately upload this spreadsheet to google docs for
others to look at, but google docs doesn't recognize this lookup formula.
Any more thoughts?

"Jacob Skaria" wrote:

In A1 enter the formula

=LOOKUP(10^10,D:D)
OR

=LOOKUP(10^10,D$1:D$100)


If this post helps click Yes
---------------
Jacob Skaria


"Rob" wrote:

I want A1 to equal the contents of a cell in the "last row with contents".
If not, then I want the cell in the row above it, and if not I want the cell
in the row above that, and so on...

For instance...

A1=5
if...
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is 5
D6 is (empty)
D7 is (empty)
...

Second Example...

A1=4
if..
D1 is 1
D2 is 2
D3 is 3
D4 is 4
D5 is (empty)
D6 is (empty)
D7 is (empty)