| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How To Retrieve Data From Last Cell In Column?
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
Since you did say TEXT, try this:
=LOOKUP(REPT("z",255),A:A) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bagheera" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
For last text value in column A
=LOOKUP(REPT("z",255),A:A) "Bagheera" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
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..." ?? "daddylonglegs" wrote: For last text value in column A =LOOKUP(REPT("z",255),A:A) "Bagheera" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
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 "Bagheera" wrote in message ... 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..." ?? "daddylonglegs" wrote: For last text value in column A =LOOKUP(REPT("z",255),A:A) "Bagheera" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... 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..." ?? "daddylonglegs" wrote: For last text value in column A =LOOKUP(REPT("z",255),A:A) "Bagheera" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
First of all, are you looking for text or a date?
-- Regards, Peo Sjoblom "Bagheera" wrote in message ... 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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
Date.
"Peo Sjoblom" wrote: First of all, are you looking for text or a date? -- Regards, Peo Sjoblom "Bagheera" wrote in message ... 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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... Date. "Peo Sjoblom" wrote: First of all, are you looking for text or a date? -- Regards, Peo Sjoblom "Bagheera" wrote in message ... 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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
Then that most likely is a number
in A1 put this =LOOKUP(99^99,A2:A65536) -- Regards, Peo Sjoblom "Bagheera" wrote in message ... Date. "Peo Sjoblom" wrote: First of all, are you looking for text or a date? -- Regards, Peo Sjoblom "Bagheera" wrote in message ... 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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
That did it. Thanks!
"T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... Date. "Peo Sjoblom" wrote: First of all, are you looking for text or a date? -- Regards, Peo Sjoblom "Bagheera" wrote in message ... 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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... 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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
You're welcome!
-- Biff Microsoft Excel MVP "Bagheera" wrote in message ... That did it. Thanks! "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... Date. "Peo Sjoblom" wrote: First of all, are you looking for text or a date? -- Regards, Peo Sjoblom "Bagheera" wrote in message ... 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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... 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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How To Retrieve Data From Last Cell In Column?
Thanks for that suggestion. Saved me a ton of trial and error!
"T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... Date. "Peo Sjoblom" wrote: First of all, are you looking for text or a date? -- Regards, Peo Sjoblom "Bagheera" wrote in message ... 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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Bagheera" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I retrieve data from the same cell, from multiple sheets? | Excel Worksheet Functions | |||
| retrieve last entry in column | Excel Worksheet Functions | |||
| HOW DO I RETRIEVE DATA FROM THE LAST UPDATED CELL IN A ROW | Excel Worksheet Functions | |||
| How do I retrieve data into a cell from a web site? | Excel Worksheet Functions | |||
| Retrieve last cell with data | Excel Worksheet Functions | |||
Linear Mode
