| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to return the last non-blank cell in a column that is TEXT
In any cell of column C of Sheet 2, I want it to return the last non-blank
cell from Sheet 1 Column D...being the last non-blank cell in or ABOVE that row. Column D in sheet 1 has TEXT and no numbers. My range I'm working from on sheet 1 is D7:D7000. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to return the last non-blank cell in a column that is TEXT
Assuming there are no formula blanks in the range:
=LOOKUP(REPT("z",255),D7:D7000) -- Biff Microsoft Excel MVP "VSlaybaugh" wrote in message ... In any cell of column C of Sheet 2, I want it to return the last non-blank cell from Sheet 1 Column D...being the last non-blank cell in or ABOVE that row. Column D in sheet 1 has TEXT and no numbers. My range I'm working from on sheet 1 is D7:D7000. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to return the last non-blank cell in a column that is TEXT
On Mon, 6 Oct 2008 15:14:11 -0700, VSlaybaugh
wrote: In any cell of column C of Sheet 2, I want it to return the last non-blank cell from Sheet 1 Column D...being the last non-blank cell in or ABOVE that row. Column D in sheet 1 has TEXT and no numbers. My range I'm working from on sheet 1 is D7:D7000. =LOOKUP(2,1/(LEN(D7:D7000)0),D7:D7000) --ron |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to return the last non-blank cell in a column that is TEXT
Sorry guys. Both of your functions produce the same results but not what I'm
after. I need for the result to return what was last TEXT in the column ABOVE or IN that particular row where the function is...not what is in the very last cell of a column. "Ron Rosenfeld" wrote: On Mon, 6 Oct 2008 15:14:11 -0700, VSlaybaugh wrote: In any cell of column C of Sheet 2, I want it to return the last non-blank cell from Sheet 1 Column D...being the last non-blank cell in or ABOVE that row. Column D in sheet 1 has TEXT and no numbers. My range I'm working from on sheet 1 is D7:D7000. =LOOKUP(2,1/(LEN(D7:D7000)0),D7:D7000) --ron |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to return the last non-blank cell in a column that is TEXT
You could enter this formula in C7 of Sheet2, and copy down to C7000:
=LOOKUP(REPT("z",255),Sheet1!D$7:D7) This will give you a row by row return of the data on Sheet1. If you want to simply enter a formula in any cell in Column C of Sheet2, from Row 7 downward, try this: =LOOKUP(REPT("z",255),Sheet1!D$7:INDEX(Sheet1!D:D, ROW())) And copy down if need be. Don't forget, start at least in Row7. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "VSlaybaugh" wrote in message ... Sorry guys. Both of your functions produce the same results but not what I'm after. I need for the result to return what was last TEXT in the column ABOVE or IN that particular row where the function is...not what is in the very last cell of a column. "Ron Rosenfeld" wrote: On Mon, 6 Oct 2008 15:14:11 -0700, VSlaybaugh wrote: In any cell of column C of Sheet 2, I want it to return the last non-blank cell from Sheet 1 Column D...being the last non-blank cell in or ABOVE that row. Column D in sheet 1 has TEXT and no numbers. My range I'm working from on sheet 1 is D7:D7000. =LOOKUP(2,1/(LEN(D7:D7000)0),D7:D7000) --ron |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to return the last non-blank cell in a column that is TEXT
Wow...your second formula worked best. I tried plugging in the first one but
I must have done something wrong. I wish I had the knowledge you all have in figuring things out like this. Thanks so much for the great help! "RagDyeR" wrote: You could enter this formula in C7 of Sheet2, and copy down to C7000: =LOOKUP(REPT("z",255),Sheet1!D$7:D7) This will give you a row by row return of the data on Sheet1. If you want to simply enter a formula in any cell in Column C of Sheet2, from Row 7 downward, try this: =LOOKUP(REPT("z",255),Sheet1!D$7:INDEX(Sheet1!D:D, ROW())) And copy down if need be. Don't forget, start at least in Row7. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "VSlaybaugh" wrote in message ... Sorry guys. Both of your functions produce the same results but not what I'm after. I need for the result to return what was last TEXT in the column ABOVE or IN that particular row where the function is...not what is in the very last cell of a column. "Ron Rosenfeld" wrote: On Mon, 6 Oct 2008 15:14:11 -0700, VSlaybaugh wrote: In any cell of column C of Sheet 2, I want it to return the last non-blank cell from Sheet 1 Column D...being the last non-blank cell in or ABOVE that row. Column D in sheet 1 has TEXT and no numbers. My range I'm working from on sheet 1 is D7:D7000. =LOOKUP(2,1/(LEN(D7:D7000)0),D7:D7000) --ron |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to return the last non-blank cell in a column that is TEXT
You're welcome, and thanks for the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "VSlaybaugh" wrote in message ... Wow...your second formula worked best. I tried plugging in the first one but I must have done something wrong. I wish I had the knowledge you all have in figuring things out like this. Thanks so much for the great help! "RagDyeR" wrote: You could enter this formula in C7 of Sheet2, and copy down to C7000: =LOOKUP(REPT("z",255),Sheet1!D$7:D7) This will give you a row by row return of the data on Sheet1. If you want to simply enter a formula in any cell in Column C of Sheet2, from Row 7 downward, try this: =LOOKUP(REPT("z",255),Sheet1!D$7:INDEX(Sheet1!D:D, ROW())) And copy down if need be. Don't forget, start at least in Row7. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "VSlaybaugh" wrote in message ... Sorry guys. Both of your functions produce the same results but not what I'm after. I need for the result to return what was last TEXT in the column ABOVE or IN that particular row where the function is...not what is in the very last cell of a column. "Ron Rosenfeld" wrote: On Mon, 6 Oct 2008 15:14:11 -0700, VSlaybaugh wrote: In any cell of column C of Sheet 2, I want it to return the last non-blank cell from Sheet 1 Column D...being the last non-blank cell in or ABOVE that row. Column D in sheet 1 has TEXT and no numbers. My range I'm working from on sheet 1 is D7:D7000. =LOOKUP(2,1/(LEN(D7:D7000)0),D7:D7000) --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find common text in cell in column in sheet then return total amou | Excel Worksheet Functions | |||
| Text to appear three rows after last blank cell in column | Excel Discussion (Misc queries) | |||
| Formula to return position of the next Non-blank cell in a column | Excel Worksheet Functions | |||
| Return next non-Blank Cell in a Column ? | Excel Worksheet Functions | |||
| Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Linear Mode
