Ref of First Non blank cell

[Sampoerna]

I need a formula to recognize the first non blank cell within an area e.g.
"B2:Z2", and the formula should displayed as address. My appreciation for
any help.

 

[pub]

I need a formula to recognize the first non blank cell within an area
e.g. "B2:Z2", and the formula should displayed as address. My
appreciation for any help.

so if the 1st non-blank is "hello" in F2 you want to show the address F2

copy&paste this array formula

=CHAR(65+MATCH(INDEX(B2:Z2,1,MATCH(TRUE,LEN(B2:Z2)<>0,0)),B2:Z2,FALSE))&2

hit Ctrl-Shift-Enter.
if you hit all 3 keys correctly you will see the curly brackets { and }
around the formula.

 

[Ron Rosenfeld]

I need a formula to recognize the first non blank cell within an area e.g.
"B2:Z2", and the formula should displayed as address. My appreciation for
any help.

This formula must be **array-entered** and will work for single row horizontal
ranges.

=ADDRESS(ROW(rng),MATCH(TRUE,NOT(ISBLANK(rng)),0))


To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron

 

[Teethless mama]

=ADDRESS(2,MATCH(TRUE,INDEX(B2:Z2<>"",),)+1)

Normally ENTER

 

[Sampoerna]

Thanks for the responds,

That formula really works. What if without { and }, is there any way to
write the formula which gives
the same result?

I'll be glad to hear if it is possible.

 

[pub]

Thanks for the responds,

That formula really works. What if without { and }, is there any way to
write the formula which gives
the same result?

I'll be glad to hear if it is possible.

i took a look at rons formula and noticed errors on my original. i put a
redundant index&match inside a match()...and i hardcoded the row at 2.
also my formula only works up to column Z. so Rons formula with address
() would be the better way to go.

heres a cleaned up formula

=CHAR(65+MATCH(TRUE,LEN(B2:Z2)<>0,0))&ROW(B2)

again,its an array, so you needs to hit ctrl-shift-enter.
now once you do that...along with the new row(b2) you can fill the
formula down the column real easy.

it needs { and } because its using match() to find the 1st non-blank
across every cell in the row. the only way around it would be to use a
sumproduct() or an offset() formula of some sort.

the len(b2:z2)<>0 looks at the length of the data in every cell in the
range and stores it stores it in memory as a "true" or "false". and this
is why you need to hit ctrl-shift-enter. if you dont, then excel wont
know to store the range into memory, and will only look at 1st cell.

the match() will match the 1st "true" in the len() range and pull the
column#...and add it to the =char(65)

 

[Shane Devenshire]

Hi,

Just came across this thread and thought I would add a few minor comments:

You can shorten the array suggestion to

=CHAR(65+MATCH(1,N(B2:Z2<>""),))&2

or make it a longer non-array version

=CHAR(65+SUMPRODUCT(MATCH(TRUE,B2:Z2<>"",)))&2

or you can use the short array formula

=ADDRESS(2,MATCH(1,N(B2:Z2<>""),)+1)

 

[Sampoerna]

Thank you all,
You guys are so great! And I feel so lucky having all of you respond to the
threat.
All the best... Cheers!