formula for first nonblank cell

[kimm199]

Hi,

I have a spreadsheet where the rows are names and the columns are dates. For each column (each date) the number 1 is entered if the person is present, it is left blank if they are not. I need two formulas (not a macro please!) that I can insert into Column B and C respectively, that automatically finds the date corresponding with the person's first and last day present. Maybe it can be a formula showing the date (as entered in row 1) that corresponds to the first non-blank cell in that row?

Thanks!

Kim

 

[Trebor76]

Hi Kim,

Welcome to MrExcel!!

Assuming the names are in Col A and the present flag (i.e. 1) is in the range $B$1:$B50 (change the 50 to suit), try these;

Col A value of first 1 found:

=INDIRECT("A"&MATCH(MIN($B$1:$B50),$B$1:$B50,0))

Col A value of last 1 found:

=INDIRECT("A" & MATCH(1,$B$1:$B50))

Though I'm sure there's other ways to achieve the same, these will do the job.

HTH

Robert

 

[littleiitin]

=MATCH(TRUE,ISBLANK(A:A),0)

Then Crtl+Enter

 

[T. Valko]

Hi,

I have a spreadsheet where the rows are names and the columns are dates. For each column (each date) the number 1 is entered if the person is present, it is left blank if they are not. I need two formulas (not a macro please!) that I can insert into Column B and C respectively, that automatically finds the date corresponding with the person's first and last day present. Maybe it can be a formula showing the date (as entered in row 1) that corresponds to the first non-blank cell in that row?

Thanks!

Kim

Try these...

Book1
	D	E	F	G	H
1	7/1/2011	7/2/2011	7/3/2011	7/4/2011	7/5/2011
2	_	1	1	1	_
Sheet3

For the first date:

=INDEX(D1:H1,MATCH(1,D2:H2,0))

Format as Date

For the last date:

=INDEX(D1:H1,MATCH(2,D2:H2))

Format as Date

 

[bebo021999]

Try this in B2:

=INDEX($D$1:$K$1,MATCH(1,$D$2:$K$2,0))

In C2:

=LOOKUP(2,$D$2:$K$2,$D$1:$K$1)

 

[kimm199]

Great! Thank you! This worked the best for what I was trying to do:

arrival date: =INDEX($D$1:$AA$1,MATCH(1,D2:AA2,0))
departure date: =INDEX($D$1:$AA$1,MATCH(2,D2:AA2))

Cheers!!!

 

[T. Valko]

Great! Thank you! This worked the best for what I was trying to do:

arrival date: =INDEX($D$1:$AA$1,MATCH(1,D2:AA2,0))
departure date: =INDEX($D$1:$AA$1,MATCH(2,D2:AA2))

Cheers!!!

You're welcome. Thanks for the feedback!