Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 6th 08, 11:14 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 5
Default 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 D77000.

  #2   Report Post  
Old October 6th 08, 11:21 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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),D77000)

--
Biff
Microsoft Excel MVP


"VSlaybaugh" wrote in message
news
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 D77000.



  #3   Report Post  
Old October 7th 08, 12:34 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default 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 D77000.



=LOOKUP(2,1/(LEN(D77000)0),D77000)

--ron
  #4   Report Post  
Old October 7th 08, 03:30 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 5
Default 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 D77000.



=LOOKUP(2,1/(LEN(D77000)0),D77000)

--ron

  #5   Report Post  
Old October 7th 08, 05:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,572
Default 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$77)

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, 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
news 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 D77000.



=LOOKUP(2,1/(LEN(D77000)0),D77000)

--ron





  #6   Report Post  
Old October 7th 08, 05:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 5
Default 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$77)

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, 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
news 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 D77000.



=LOOKUP(2,1/(LEN(D77000)0),D77000)

--ron




  #7   Report Post  
Old October 8th 08, 03:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,572
Default 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$77)

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, 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
news 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 D77000.



=LOOKUP(2,1/(LEN(D77000)0),D77000)

--ron








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
find common text in cell in column in sheet then return total amou leeona Excel Worksheet Functions 1 June 7th 08 04:43 AM
Text to appear three rows after last blank cell in column Monk[_2_] Excel Discussion (Misc queries) 3 May 16th 08 04:04 AM
Formula to return position of the next Non-blank cell in a column PCLIVE Excel Worksheet Functions 14 July 17th 07 01:11 PM
Return next non-Blank Cell in a Column ? Jakobshavn Isbrae Excel Worksheet Functions 3 May 23rd 07 10:28 PM
Find First Non blank cell than find column header and return that value Silver Rose Excel Worksheet Functions 10 April 30th 07 05:56 PM


All times are GMT +1. The time now is 12:01 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017