find the first and last non blank cell in a row
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
|