How to return the last non-blank cell in a column that is TEXT

  • Thread starter Thread starter VSlaybaugh
  • Start date Start date
V

VSlaybaugh

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.
 
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.
Click to expand...


=LOOKUP(2,1/(LEN(D7:D7000)>0),D7:D7000)

--ron
 
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.
 
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!
=====================================================



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.
 
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!
 
You're welcome, and thanks for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

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!
 
VSlaybaugh said:
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
> <[email protected]> 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
>
Click to expand...

I am not sure what the smileys are about as I am very new with Excel But if you want to find the last Cell in a column with text and did not that you could use smileys in an Excel formula I use this formula and it does very nicely to find the last text cell in a column on my next sheet.,

=INDEX(Sep13WS!J9:J98,MATCH(REPT("z",255),Sep13WS!J9:J98))

Oh yes I aware that a lot of water has flown under the bridge since this message was posted, however this may be useful other newbies like myself :cheers:
 
Back
Top