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?
|