Search code examples
excelif-statementexcel-formulaz-indexoffset

Drag formula and have it reference every other cell

I have a simple if statement that I made that works. However, when I try to drag the formula so that it automatically populates the other cells, it doesn't reference the correct cells. That is because it increments the reference cell chronologically (i.e: goes from X to Y to Z) but I need it to increment every other cell (i.e: X to Z to AB) how would I do that?

Here is the snippet of the place where my formula is:

enter image description here

Here is a snippet of what I want it to reference:

enter image description here

Solution

  • You can get X3, Z3, AB3 etc by filling the following right.

    =INDEX($X:$CP, ROW(3:3), (COLUMN(A:A)-1)*2+1)

    This makes your formula in CQ3,

    =IF(INDEX($X:$CP, ROW(3:3), (COLUMN(A:A)-1)*2+1)>1, 2, IF(INDEX($X:$CP, ROW(3:3), (COLUMN(A:A)-1)*2+1)=1, 1, 0))

    Fill down for row 4.