First Occurence of Non Blank Cell in row array
 

I have an array of yearly dates in A2:A20. I then have
an array below it B2:B20 that the user can enter costs
into. I want to be able to identify the year that the
first cost appears in. The cells that do not have
numbers will be left blank. How do I do this? I know I
can count the occurences of cells with data:

Sumproduct(--(B2:B200))

But how do I identify which column or year the first of
these occurs in?

Thanks

figured it out with an array formula

{Index(A2:A20,Match(TRUE,NOT(ISBLANK(B2:B20)),0)}




-----Original Message-----
I have an array of yearly dates in A2:A20. I then have
an array below it B2:B20 that the user can enter costs
into. I want to be able to identify the year that the
first cost appears in. The cells that do not have
numbers will be left blank. How do I do this? I know I
can count the occurences of cells with data:

Sumproduct(--(B2:B200))

But how do I identify which column or year the first of
these occurs in?

Thanks
.

=INDEX(A2:A20,MATCH(1,1/(LEN(B2:B20)0),0))

Array-entered. Press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
I have an array of yearly dates in A2:A20. I then have
an array below it B2:B20 that the user can enter costs
into. I want to be able to identify the year that the
first cost appears in. The cells that do not have
numbers will be left blank. How do I do this? I know I
can count the occurences of cells with data:

Sumproduct(--(B2:B200))

But how do I identify which column or year the first of
these occurs in?

Thanks
.

Just out of curiosity, my model crashes with I enter my
array formula. I am copying it down 200 rows. Does
anyone know of a solution that does not involve an array
formula?

Thanks


-----Original Message-----
=INDEX(A2:A20,MATCH(1,1/(LEN(B2:B20)0),0))

Array-entered. Press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
I have an array of yearly dates in A2:A20. I then have
an array below it B2:B20 that the user can enter costs
into. I want to be able to identify the year that the
first cost appears in. The cells that do not have
numbers will be left blank. How do I do this? I know
I
can count the occurences of cells with data:

Sumproduct(--(B2:B200))

But how do I identify which column or year the first of
these occurs in?

Thanks
.

.

Model would crash if I copied array formula down to many
rows at once. Copies a few rows at a time and it seems
to work now???? Not sure why but it works.




-----Original Message-----
Just out of curiosity, my model crashes with I enter my
array formula. I am copying it down 200 rows. Does
anyone know of a solution that does not involve an array
formula?

Thanks


-----Original Message-----
=INDEX(A2:A20,MATCH(1,1/(LEN(B2:B20)0),0))

Array-entered. Press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
I have an array of yearly dates in A2:A20. I then
have
an array below it B2:B20 that the user can enter costs
into. I want to be able to identify the year that the
first cost appears in. The cells that do not have
numbers will be left blank. How do I do this? I know
I
can count the occurences of cells with data:

Sumproduct(--(B2:B200))

But how do I identify which column or year the first
of
these occurs in?

Thanks
.

.

.