G
Guest
Hello,
I am hoping that some kind person may be able to help with this problem.
I have a spreadsheet that can contain a value in column A to column AA or
even greater.
A simple example is this
A B C D E F G H I J K L
1 4 5 6 7 8 9 10 11 12 13 14 15
2 x x x x x x x x x x x x
3 x x x x x
4 x x x x x
I am using the below formulas (thanks to bob) to return the corresponding
value in row 1 for the first and last ocurrence of x in rows 2,3 and so on.
These formulas work fine however I would like to restrict the indexing to a
maximum of 4 columns. For example,in the above I would like row 2 to return
the values 4 and 7 for the first block of 4 x's, then 8 and 11 for the second
block,12 and 15 for the third block.
Row 2 would return 6 and 9 for the first and 10 and 10 for the second block.
I hope that this explains what I am after.
The current formulas return 1 and 15 for row 1, 6 and 10 fro row 2 and 10
and 14 for row 3.
=INDEX($104:$104,MIN(IF($E110:$AX110<>"",COLUMN($E110:$AX110))))
=INDEX($104:$104,MAX(IF($E110:$AX110<>"",COLUMN($E110:$AX110))))
Many Thanks
Allan
I am hoping that some kind person may be able to help with this problem.
I have a spreadsheet that can contain a value in column A to column AA or
even greater.
A simple example is this
A B C D E F G H I J K L
1 4 5 6 7 8 9 10 11 12 13 14 15
2 x x x x x x x x x x x x
3 x x x x x
4 x x x x x
I am using the below formulas (thanks to bob) to return the corresponding
value in row 1 for the first and last ocurrence of x in rows 2,3 and so on.
These formulas work fine however I would like to restrict the indexing to a
maximum of 4 columns. For example,in the above I would like row 2 to return
the values 4 and 7 for the first block of 4 x's, then 8 and 11 for the second
block,12 and 15 for the third block.
Row 2 would return 6 and 9 for the first and 10 and 10 for the second block.
I hope that this explains what I am after.
The current formulas return 1 and 15 for row 1, 6 and 10 fro row 2 and 10
and 14 for row 3.
=INDEX($104:$104,MIN(IF($E110:$AX110<>"",COLUMN($E110:$AX110))))
=INDEX($104:$104,MAX(IF($E110:$AX110<>"",COLUMN($E110:$AX110))))
Many Thanks
Allan