Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 45
Default First Non-Blank Cell

I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

....

but what if I want to find the first non-blank vs the last non-blank?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default First Non-Blank Cell

Here's one way

=INDEX(A1:A100,MATCH(TRUE,LEN(A1:A100)<0,0),1)

or to find the row

=MATCH(FALSE,ISBLANK(A1:A100),0)

These are array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"FrankM" wrote:

I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

...

but what if I want to find the first non-blank vs the last non-blank?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default First Non-Blank Cell

I assume you're considering cells that might have formulas that return
formula blanks ("") as blanks and should be ignored.

One way:

=INDEX(A1:A100,MATCH(TRUE,INDEX(A1:A100<"",,1),0) )

--
Biff
Microsoft Excel MVP


"FrankM" wrote in message
...
I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

...

but what if I want to find the first non-blank vs the last non-blank?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 45
Default First Non-Blank Cell

Unfortunately arrays don't help me. Thank you though ... looked promising.


"Mike H" wrote:

Here's one way

=INDEX(A1:A100,MATCH(TRUE,LEN(A1:A100)<0,0),1)

or to find the row

=MATCH(FALSE,ISBLANK(A1:A100),0)

These are array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"FrankM" wrote:

I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

...

but what if I want to find the first non-blank vs the last non-blank?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default First Non-Blank Cell

Hi,

The following array entered formula will do that

=INDEX(C3:C19,MATCH(TRUE,C3:C19<"",0))

Press Shift+Ctrl+Enter to enter it.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"FrankM" wrote:

I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

...

but what if I want to find the first non-blank vs the last non-blank?



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,805
Default First Non-Blank Cell

If you want the row no of first non-blank cell then paste this in a cell
=INDEX(MATCH(1,(--(A1:A100<"")),0),1)
and press CTRL-SHIFT-ENTER

If you want the value in first non-blank cell then use
=INDIRECT("A"&MATCH(1,(--(A1:A100<"")),0))
and press CTRL-SHIFT-ENTER

I am sure there will be a simpler formula...

"FrankM" wrote:

I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

...

but what if I want to find the first non-blank vs the last non-blank?

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default First Non-Blank Cell

If you don't like array formulae, we can use a helper column. Say you data
is in column B, from B1 thru B1000. In A1 enter:

=IF(B1="","",1) and copy down

The first non-blank value in column B is then:

=VLOOKUP(1,A1:B1000,2,FALSE)

no arrays are needed.
--
Gary''s Student - gsnu200813


"FrankM" wrote:

Unfortunately arrays don't help me. Thank you though ... looked promising.


"Mike H" wrote:

Here's one way

=INDEX(A1:A100,MATCH(TRUE,LEN(A1:A100)<0,0),1)

or to find the row

=MATCH(FALSE,ISBLANK(A1:A100),0)

These are array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"FrankM" wrote:

I found this formula somewhere else on these boards ...


The VALUE of the last NON-BLANK cell in a COLUMN range
=LOOKUP(2,1/(A1:A100<""),A1:A100)

...

but what if I want to find the first non-blank vs the last non-blank?

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
returning blank when reference cell is blank mslylan2u Excel Worksheet Functions 4 June 6th 08 09:09 PM
Average Formula to display blank cell if named range is blank Rachael F Excel Worksheet Functions 3 February 22nd 08 06:05 PM
how to get excel to display blank if reference cell blank silent1(not) Excel Worksheet Functions 1 December 2nd 05 03:49 PM
How do I make a blank cell with a date format blank? Pivot Table/Query Excel Worksheet Functions 6 June 14th 05 11:19 PM
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. QUEST41067 Excel Discussion (Misc queries) 1 January 15th 05 10:29 PM


All times are GMT +1. The time now is 02:20 PM.

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

About Us

"It's about Microsoft Excel"