software

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:$H2lt;gt;quot;quot;,COLUMN($C2:$H2))))

and

=INDEX($1:$1,MAX(IF($C2:$H2lt;gt;quot;quot;,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)

quot;Allan from Melbournequot; gt; wrote
in message ...
gt; Hello,
gt; I have a sheet that resembles this
gt;
gt; A B C D E F
gt; G H
gt; 1 START FINISH 04:00 05:00 06:00 07:00 08:00 09:00
gt; 2 D D D
gt; 3 D I I
D
gt; D
gt; 3
gt; I I I
gt;
gt; I am looking for a formula to put in A:2 that will return the value in Row
gt; 1x where x is the column reference of the first non blank cell in row two.
gt; I am also looking for a formula toi put in B:2 that will return the value
in
gt; Row 1x where x is the cloum reference number of the last cell that
contains a
gt; value in row 2. These formulas will be repeated for each row.
gt;
gt; I hope that you can understand my request.
gt; Many Thanks
gt; 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 quot;offsetquot; with this array formula however I am given an error.
Thanks
Allan

quot;Bob Phillipsquot; wrote:

gt; =INDEX($1:$1,MIN(IF($C2:$H2lt;gt;quot;quot;,COLUMN($C2:$H2))))
gt;
gt; and
gt;
gt; =INDEX($1:$1,MAX(IF($C2:$H2lt;gt;quot;quot;,COLUMN($C2:$H2))))
gt;
gt; which is an array formula, it should be committed with Ctrl-Shift-Enter, not
gt; just Enter.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove xxx from email address if mailing direct)
gt;
gt; quot;Allan from Melbournequot; gt; wrote
gt; in message ...
gt; gt; Hello,
gt; gt; I have a sheet that resembles this
gt; gt;
gt; gt; A B C D E F
gt; gt; G H
gt; gt; 1 START FINISH 04:00 05:00 06:00 07:00 08:00 09:00
gt; gt; 2 D D D
gt; gt; 3 D I I
gt; D
gt; gt; D
gt; gt; 3
gt; gt; I I I
gt; gt;
gt; gt; I am looking for a formula to put in A:2 that will return the value in Row
gt; gt; 1x where x is the column reference of the first non blank cell in row two.
gt; gt; I am also looking for a formula toi put in B:2 that will return the value
gt; in
gt; gt; Row 1x where x is the cloum reference number of the last cell that
gt; contains a
gt; gt; value in row 2. These formulas will be repeated for each row.
gt; gt;
gt; gt; I hope that you can understand my request.
gt; gt; Many Thanks
gt; gt; Allan
gt;
gt;
gt;

Bob,
Thanks, I managed to work it out using quot;offsetquot;.
Regards
Allan

quot;Allan from Melbournequot; wrote:

gt; Dear Bob,
gt; Many thanks for the response. It worked except I did not provide you with
gt; the correct information in the first place. On the second index array formula
gt; I would like to return the value of the Row 1x where x is the column where
gt; the first non blank cell appears after the run of populated cells. I have
gt; tried combining quot;offsetquot; with this array formula however I am given an error.
gt; Thanks
gt; Allan
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; =INDEX($1:$1,MIN(IF($C2:$H2lt;gt;quot;quot;,COLUMN($C2:$H2))))
gt; gt;
gt; gt; and
gt; gt;
gt; gt; =INDEX($1:$1,MAX(IF($C2:$H2lt;gt;quot;quot;,COLUMN($C2:$H2))))
gt; gt;
gt; gt; which is an array formula, it should be committed with Ctrl-Shift-Enter, not
gt; gt; just Enter.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove xxx from email address if mailing direct)
gt; gt;
gt; gt; quot;Allan from Melbournequot; gt; wrote
gt; gt; in message ...
gt; gt; gt; Hello,
gt; gt; gt; I have a sheet that resembles this
gt; gt; gt;
gt; gt; gt; A B C D E F
gt; gt; gt; G H
gt; gt; gt; 1 START FINISH 04:00 05:00 06:00 07:00 08:00 09:00
gt; gt; gt; 2 D D D
gt; gt; gt; 3 D I I
gt; gt; D
gt; gt; gt; D
gt; gt; gt; 3
gt; gt; gt; I I I
gt; gt; gt;
gt; gt; gt; I am looking for a formula to put in A:2 that will return the value in Row
gt; gt; gt; 1x where x is the column reference of the first non blank cell in row two.
gt; gt; gt; I am also looking for a formula toi put in B:2 that will return the value
gt; gt; in
gt; gt; gt; Row 1x where x is the cloum reference number of the last cell that
gt; gt; contains a
gt; gt; gt; value in row 2. These formulas will be repeated for each row.
gt; gt; gt;
gt; gt; gt; I hope that you can understand my request.
gt; gt; gt; Many Thanks
gt; gt; gt; Allan
gt; gt;
gt; gt;
gt; gt;