Can someone help me out with a formula that will return the value of the
first non empty cell of a column.
And, also a formula that will return the value of the first non blank cell
of a column.
Thank you

Since you're distinguishing empty from blank then I'm assuming you know the
difference between the two. A cell can be blank but not empty. If the first
non-empty cell contains a formula blank then the result of that formula will
be the formula blank

Both formulas are array formulas**

For the first non-empty cell:

=INDEX(A1:A10,MATCH(1,SUBTOTAL(3,OFFSET(A1:A10,,,R OW(A1:A10)-MIN(ROW(A1))+1)),0))

For the first non-blank cell:

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

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"danpt" wrote in message
...
Can someone help me out with a formula that will return the value of the
first non empty cell of a column.
And, also a formula that will return the value of the first non blank cell
of a column.
Thank you

Hi,

Here is one way:

INDEX(A1:A22,MIN(IF((A1:A22<"")*(ROW(A1:A22))0,R OW(A1:A22),"")))

This is an arry entered formula so press Shift+Ctrl+Enter instead of Enter.

Regarding the second question, in excel a non empty cell and a non blank
cell are the same thing. If you mean something different you should explainl

However, if you mean that one of the cells contains a formula which returns
"" and you want to consider that as nonblank then:

=INDEX(A1:A22,MIN(IF((ISBLANK(A1:A22))*(ROW(A1:A22 ))0,ROW(A1:A22),"")))

Also array entered.


If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"danpt" wrote:

Can someone help me out with a formula that will return the value of the
first non empty cell of a column.
And, also a formula that will return the value of the first non blank cell
of a column.
Thank you

Thank you very much.

"T. Valko" wrote:

Since you're distinguishing empty from blank then I'm assuming you know the
difference between the two. A cell can be blank but not empty. If the first
non-empty cell contains a formula blank then the result of that formula will
be the formula blank

Both formulas are array formulas**

For the first non-empty cell:

=INDEX(A1:A10,MATCH(1,SUBTOTAL(3,OFFSET(A1:A10,,,R OW(A1:A10)-MIN(ROW(A1))+1)),0))

For the first non-blank cell:

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

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"danpt" wrote in message
...
Can someone help me out with a formula that will return the value of the
first non empty cell of a column.
And, also a formula that will return the value of the first non blank cell
of a column.
Thank you

=INDEX(A1:A22,MIN(IF((ISBLANK(A1:A22))*(ROW(A1:A2 2))0,ROW(A1:A22),"")))

Not sure what the intended result is supposed to be but it doesn't seem to
work correctly for me.

If I fill the entire range with numbers 0 it correctly returns the value
from A1. If I clear cell A2 (now an empty cell) then it retuns 0. Shouldn't
it still return the value from A1?


--
Biff
Microsoft Excel MVP


"ShaneDevenshire" wrote in
message ...
Hi,

Here is one way:

INDEX(A1:A22,MIN(IF((A1:A22<"")*(ROW(A1:A22))0,R OW(A1:A22),"")))

This is an arry entered formula so press Shift+Ctrl+Enter instead of
Enter.

Regarding the second question, in excel a non empty cell and a non blank
cell are the same thing. If you mean something different you should
explainl

However, if you mean that one of the cells contains a formula which
returns
"" and you want to consider that as nonblank then:

=INDEX(A1:A22,MIN(IF((ISBLANK(A1:A22))*(ROW(A1:A22 ))0,ROW(A1:A22),"")))

Also array entered.


If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"danpt" wrote:

Can someone help me out with a formula that will return the value of the
first non empty cell of a column.
And, also a formula that will return the value of the first non blank
cell
of a column.
Thank you

Hi, Shane
Thanks to T.Valko with
=INDEX(A1:A10,MATCH(1,SUBTOTAL(3,OFFSET(A1:A10,,,R OW(A1:A10)-MIN(ROW(A1))+1)),0))
A non empty cell is a cell either with ' or has a formula =IF(B1="","",B2)
in cell(A5) for example.
Thank you

"ShaneDevenshire" wrote:

Hi,

Here is one way:

INDEX(A1:A22,MIN(IF((A1:A22<"")*(ROW(A1:A22))0,R OW(A1:A22),"")))

This is an arry entered formula so press Shift+Ctrl+Enter instead of Enter.

Regarding the second question, in excel a non empty cell and a non blank
cell are the same thing. If you mean something different you should explainl

However, if you mean that one of the cells contains a formula which returns
"" and you want to consider that as nonblank then:

=INDEX(A1:A22,MIN(IF((ISBLANK(A1:A22))*(ROW(A1:A22 ))0,ROW(A1:A22),"")))

Also array entered.


If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"danpt" wrote:

Can someone help me out with a formula that will return the value of the
first non empty cell of a column.
And, also a formula that will return the value of the first non blank cell
of a column.
Thank you

You're welcome!

--
Biff
Microsoft Excel MVP


"danpt" wrote in message
...
Thank you very much.

"T. Valko" wrote:

Since you're distinguishing empty from blank then I'm assuming you know
the
difference between the two. A cell can be blank but not empty. If the
first
non-empty cell contains a formula blank then the result of that formula
will
be the formula blank

Both formulas are array formulas**

For the first non-empty cell:

=INDEX(A1:A10,MATCH(1,SUBTOTAL(3,OFFSET(A1:A10,,,R OW(A1:A10)-MIN(ROW(A1))+1)),0))

For the first non-blank cell:

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

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"danpt" wrote in message
...
Can someone help me out with a formula that will return the value of
the
first non empty cell of a column.
And, also a formula that will return the value of the first non blank
cell
of a column.
Thank you