How To Retrieve Data From Last Cell In Column?

[Bagheera]

I'm trying to create a formula that will retrieve the text from the last cell
containing text in a column. No calculation necessary. Trying to keep the top
cell in a column updated with whatever the last entry in that column is...

Bruce

 

[daddylonglegs]

For last text value in column A

=LOOKUP(REPT("z",255),A:A)

 

[T. Valko]

Try this:

=LOOKUP(REPT("z",255),A:A)

That will return the last TEXT entry from column A. Note that that also
includes formula blanks ("") which are text.

 

[Bagheera]

Thanks!

I tried it, but get "Microsoft Excel cannot calculate a formula. Cell
references in the formula refer to the formula's result, creating a circular
reference..."

??

 

[Bagheera]

You guys are awesome. Thanks for helping...

The column is formatted for dates. After the "circular reference" dialog,
the cell fills with "1/0/1900." The bottom cell entry is "7/28/2008."

sigh.

B

 

[Peo Sjoblom]

You can't obviously put that formula in column A, if you want to do that
change A:A
to A2:A65535 provided that you put it in A1

--


Regards,


Peo Sjoblom

 

[T. Valko]

Don't put the formula *within* the referenced range. If you used the entire
column as the range reference then put the formula in a different column.
Examples:

=LOOKUP(REPT("z",255),A:A)
Put this formula in a column other than column A.

=LOOKUP(REPT("z",255),A1:A100)
Put this formula in any cell other than those from A1:A100.

 

[Peo Sjoblom]

First of all, are you looking for text or a date?

--


Regards,


Peo Sjoblom

 

[Bagheera]

Date.

First of all, are you looking for text or a date?

--


Regards,


Peo Sjoblom

 

[T. Valko]

If you haven't "messed" with the format a true Excel date is just a number
formatted to look like a date. So, you need to look for the *last numeric*
value in the range.

=LOOKUP(1E100,A:A)

Format as DATE

That will return the last number from column A. Do not enter the formula
*within* the referenced range otherwise you'll get the circular reference
warnings.

 

[Peo Sjoblom]

Then that most likely is a number

in A1 put this


=LOOKUP(99^99,A2:A65536)

--


Regards,


Peo Sjoblom

 

[Bagheera]

That did it. Thanks!

If you haven't "messed" with the format a true Excel date is just a number
formatted to look like a date. So, you need to look for the *last numeric*
value in the range.

=LOOKUP(1E100,A:A)

Format as DATE

That will return the last number from column A. Do not enter the formula
*within* the referenced range otherwise you'll get the circular reference
warnings.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

That did it. Thanks!

 

[JR Hester]

Thanks for that suggestion. Saved me a ton of trial and error!