Assume I have the following table:
| Row nr | Col A | Col B | Col C |
|---|---|---|---|
| 1 | Field with Formula | – | – |
| 2 | =A1 | – | – |
| 3 | =A2 | – | – |
| 4 | =A3 | – | Field to check for “word” |
| 5 | =A4 | – | – |
| 6 | =A5 | – | – |
| 7 | =A6 | – | – |
| 8 | Next Field with Formula | – | – |
| 9 | =A8 | – | – |
| 10 | =A9 | – | – |
| 11 | =A10 | – | Next field to check |
| 12 | =A11 | – | – |
| 13 | =A12 | – | – |
| 14 | =A13 | – | – |
| 15 | Task repeats… | … | … |
When I use in A1 (Field with Formula)
=IF(ISNUMBER(SEARCH("Word";C4));"With Word";"Does not contain"), I correctly receive: “With Word”.
When I use in Field A8 (Next Field with Formula) =IF(ISNUMBER(SEARCH("Word";C11));"With Word";"Does not contain"), I correctly receive: “Does not contain”.
However, I always have to copy blocks of eight rows in column A down in order to have the same structure in the next block of cells, can’t use autofill.
So, I tried using =IF(ISNUMBER(SEARCH("Word";ADDRESS(ROW()+3;COLUMN()+2)));"With Word";"Does not contain"), however, this ALWAYS(i.e.,in both, A1 and A8) “Does not contain”. Which is obviously a mistake.
So, now I use =IF(ISNUMBER(SEARCH("Word", INDEX(C:C, ROW()+3))), "With Word", "Does not contain") in all the fields with formula text in it, however, I still have to manually copy all blocks of seven. How to make Excel “learn this pattern”, so I can drag it down and it copies appropriately?
