Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with index to return particular cell value
Many thanks to Bob Phillips for getting me this far.
Thanks, it's on the right track but not quite there. Placing your 1st formula =INDEX($1:$1,MIN(IF($E2:$AX2<"",COLUMN($E2:$AX2)) )) in A2 returns the value 4 (E1). (see example below) Then I place your new formula =INDEX($1:$1,MAX(IF($A2:INDEX(2:2,MIN(IF($A2:$AX2< "",COLUMN($A2:$AX2)))+4)<"",COLUMN($A2:INDEX(2:2 ,MIN(IF($A2:$AX2<"",COLUMN($A2:$AX2)))+4))))) in B2 which returns the value 8 (I1). This is fine, what I now require is a formula for C2 which will return the value 9 (J1) (this ignores what was accounted for by the first two formulas A2 B2) and then another max formula in D2 which will then return the value of 11 (L1). I then repeat these formulas in the other rows. I hope that this makes sence. Thanks Allan A B C D E F G H I J K L M N O P 1 4 5 6 7 8 9 10 11 12 13 14 15 2 x x x x x x x x 3 x x x x x 4 x x x x x "Bob Phillips" wrote: Allan, Is this what you mean? =INDEX($1:$1,MAX(IF($A2:INDEX(2:2,MIN(IF($A2:$AX2< "",COLUMN($A2:$AX2)))+4)<"",COLUMN($A2:INDEX(2:2 ,MIN(IF($A2:$AX2<"",COLUMN($A2:$AX2)))+4))))) -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "Allan from Melbourne" wrote in message ... 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($E 110:$AX110)))) =INDEX($104:$104,MAX(IF($E110:$AX110<"",COLUMN($E 110:$AX110)))) Many Thanks Allan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
How do you make cell 2 return data if cell 1 contains text? | Excel Discussion (Misc queries) | |||
Select cell, Copy it, Paste it, Return to Previous cell | Excel Discussion (Misc queries) | |||
return zero from a blank cell | Excel Worksheet Functions |