Hello,
I have a sheet that resembles this

A B C D E F
G H
1 START FINISH 04:00 05:00 06:00 07:00 08:00 09:00
2 D D D
3 D I I D
D
3
I I I

I am looking for a formula to put in A:2 that will return the value in Row
1x where x is the column reference of the first non blank cell in row two.
I am also looking for a formula toi put in B:2 that will return the value in
Row 1x where x is the cloum reference number of the last cell that contains a
value in row 2. These formulas will be repeated for each row.

I hope that you can understand my request.
Many Thanks
Allan

=INDEX($1:$1,MIN(IF($C2:$H2<"",COLUMN($C2:$H2))))

and

=INDEX($1:$1,MAX(IF($C2:$H2<"",COLUMN($C2:$H2))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Allan from Melbourne" wrote
in message ...
Hello,
I have a sheet that resembles this

A B C D E F
G H
1 START FINISH 04:00 05:00 06:00 07:00 08:00 09:00
2 D D D
3 D I I
D
D
3
I I I

I am looking for a formula to put in A:2 that will return the value in Row
1x where x is the column reference of the first non blank cell in row two.
I am also looking for a formula toi put in B:2 that will return the value
in
Row 1x where x is the cloum reference number of the last cell that
contains a
value in row 2. These formulas will be repeated for each row.

I hope that you can understand my request.
Many Thanks
Allan

Dear Bob,
Many thanks for the response. It worked except I did not provide you with
the correct information in the first place. On the second index array formula
I would like to return the value of the Row 1x where x is the column where
the first non blank cell appears after the run of populated cells. I have
tried combining "offset" with this array formula however I am given an error.
Thanks
Allan

"Bob Phillips" wrote:

=INDEX($1:$1,MIN(IF($C2:$H2<"",COLUMN($C2:$H2))))

and

=INDEX($1:$1,MAX(IF($C2:$H2<"",COLUMN($C2:$H2))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Allan from Melbourne" wrote
in message ...
Hello,
I have a sheet that resembles this

A B C D E F
G H
1 START FINISH 04:00 05:00 06:00 07:00 08:00 09:00
2 D D D
3 D I I
D
D
3
I I I

I am looking for a formula to put in A:2 that will return the value in Row
1x where x is the column reference of the first non blank cell in row two.
I am also looking for a formula toi put in B:2 that will return the value
in
Row 1x where x is the cloum reference number of the last cell that
contains a
value in row 2. These formulas will be repeated for each row.

I hope that you can understand my request.
Many Thanks
Allan

Bob,
Thanks, I managed to work it out using "offset".
Regards
Allan

"Allan from Melbourne" wrote:

Dear Bob,
Many thanks for the response. It worked except I did not provide you with
the correct information in the first place. On the second index array formula
I would like to return the value of the Row 1x where x is the column where
the first non blank cell appears after the run of populated cells. I have
tried combining "offset" with this array formula however I am given an error.
Thanks
Allan

"Bob Phillips" wrote:

=INDEX($1:$1,MIN(IF($C2:$H2<"",COLUMN($C2:$H2))))

and

=INDEX($1:$1,MAX(IF($C2:$H2<"",COLUMN($C2:$H2))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Allan from Melbourne" wrote
in message ...
Hello,
I have a sheet that resembles this

A B C D E F
G H
1 START FINISH 04:00 05:00 06:00 07:00 08:00 09:00
2 D D D
3 D I I
D
D
3
I I I

I am looking for a formula to put in A:2 that will return the value in Row
1x where x is the column reference of the first non blank cell in row two.
I am also looking for a formula toi put in B:2 that will return the value
in
Row 1x where x is the cloum reference number of the last cell that
contains a
value in row 2. These formulas will be repeated for each row.

I hope that you can understand my request.
Many Thanks
Allan