Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning blank when reference cell is blank | Excel Worksheet Functions | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
how to get excel to display blank if reference cell blank | Excel Worksheet Functions | |||
How do I make a blank cell with a date format blank? | Excel Worksheet Functions | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) |