First nonblank cell in a row

[Rosetta Stone]

Help!

I have a sheet of data, approx 80 columns wide and 1000 rows long. I
want to do a per-row formula, and I need a pointer to the first
nonblank data in the row. [most rows start out with some blank (not
zero!) cells]

If I had a pointer to it, or if I knew how many columns over it was
from the start, I'd be set. Any ideas?

I've been reading an Excel book and searching archives of this group,
to no avail.

-Rosetta

 

[Frank Kabel]

Hi Rosetta
one way to get the row number of the first non blank in a column: Try
the following array formula (entered with CTRL+SHIFT+ENTER):
=MIN(IF(A1:A1000="",ROW(A1:A1000),1000))

HTH
Frank

 

[Ron Rosenfeld]

Help!

I have a sheet of data, approx 80 columns wide and 1000 rows long. I
want to do a per-row formula, and I need a pointer to the first
nonblank data in the row. [most rows start out with some blank (not
zero!) cells]

If I had a pointer to it, or if I knew how many columns over it was
from the start, I'd be set. Any ideas?

I've been reading an Excel book and searching archives of this group,
to no avail.

-Rosetta

The *array-entered* formula: =MATCH(FALSE,ISBLANK(2:2),0) will give the column
number of the first non-blank cell in row 2.

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

Depending on what you want to do with that information,

=ADDRESS(2,MATCH(FALSE,ISBLANK(2:2),0)) (array-entered) will give the
address as text.
=INDEX(2:2,,MATCH(FALSE,ISBLANK(2:2),0)) (array-entered) will give the
contents of that cell.


--ron

 

[Rosetta Stone]

Hi Rosetta
one way to get the row number of the first non blank in a column: Try
the following array formula (entered with CTRL+SHIFT+ENTER):
=MIN(IF(A1:A1000="",ROW(A1:A1000),1000))

Thankyouthankyouthankyou! It worked like a charm.

-Rosetta