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.

"Sampoerna" wrote in
:

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.

On Sun, 1 Mar 2009 08:12:21 +0800, "Sampoerna"
wrote:

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

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

Normally ENTER


"Sampoerna" wrote:

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.

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.

"Sampoerna" wrote in
:

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)

Hi Pub,

Your second formula is much easier to follow.
=CHAR(65+MATCH(TRUE,LEN(B2:Z2)<0,0))&ROW(B2)

I agree that Ron's formula is much much better, but the result is not quite
right.
Therefore, I added (+1) at the end of the formula e.g.
=ADDRESS(ROW(rng),MATCH(TRUE,NOT(ISBLANK(rng)),0)+ 1)

Thanks to both of you. Both ideas and examples help me alot on my works.

Thanks again.

On Sun, 1 Mar 2009 20:06:11 +0800, "Sampoerna"
wrote:

Hi Pub,

Your second formula is much easier to follow.
=CHAR(65+MATCH(TRUE,LEN(B2:Z2)<0,0))&ROW(B2)

I agree that Ron's formula is much much better, but the result is not quite
right.
Therefore, I added (+1) at the end of the formula e.g.
=ADDRESS(ROW(rng),MATCH(TRUE,NOT(ISBLANK(rng)),0) +1)

Thanks to both of you. Both ideas and examples help me alot on my works.

Thanks again.

Yes, my formula incorrectly assumed that rng began in column A. Your
modification assumes that rng always begins in column B.

To generalize the function, so it will give the correct answer no matter what
column rng begins in, try this **array** formula

=ADDRESS(ROW(rng),COLUMN(rng)-1+MATCH(TRUE,NOT(ISBLANK(rng)),0))
--ron

Thanks Ron,

That was a nice one. No doubt about the result and really fantastic!

Many thanks.

Cheers ;)

On Mon, 2 Mar 2009 00:46:13 +0800, "Sampoerna"
wrote:

Thanks Ron,

That was a nice one. No doubt about the result and really fantastic!

Many thanks.

Cheers ;)

You're welcome. Glad to help. Thanks for the feedback.
--ron

Here's a non-array version:

Normally entered:

=ADDRESS(ROW(rng),MATCH(TRUE,INDEX(rng<"",0),0)+C OLUMN(rng)-1,4)

--
Biff
Microsoft Excel MVP


"Sampoerna" wrote in message
...
Thanks Ron,

That was a nice one. No doubt about the result and really fantastic!

Many thanks.

Cheers ;)

Unbelievable! That was cool.

Thank T.Valko


"T. Valko" wrote in message
...
Here's a non-array version:

Normally entered:

=ADDRESS(ROW(rng),MATCH(TRUE,INDEX(rng<"",0),0)+C OLUMN(rng)-1,4)

--
Biff
Microsoft Excel MVP

You're welcome!

--
Biff
Microsoft Excel MVP


"Sampoerna" wrote in message
...
Unbelievable! That was cool.

Thank T.Valko


"T. Valko" wrote in message
...
Here's a non-array version:

Normally entered:

=ADDRESS(ROW(rng),MATCH(TRUE,INDEX(rng<"",0),0)+C OLUMN(rng)-1,4)

--
Biff
Microsoft Excel MVP

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)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Sampoerna" wrote:

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.

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!