My goal is to autofill every column in a spreadsheet with a formula that automatically increments the referenced row as i copy it across columns. I've seen others reference using Indirect but I've ran into a few issues (I'm no excel expert, this spreadsheet is the deepest I've delved into formulas)

I'm trying to adapt the following formula such that the results is:

Cell B2:

=IF((MOD(($A2 (52-Sheet2!$E$2)),Sheet2!$D$2)=0),1,0)*Sheet2!$C$2

Cell C2:

=IF((MOD(($A2 (52-Sheet2!$E$3)),Sheet2!$D$3)=0),1,0)*Sheet2!$C$3

Cell D2:

=IF((MOD(($A2 (52-Sheet2!$E$4)),Sheet2!$D$4)=0),1,0)*Sheet2!$C$4

So far I've tried following this tutorial: https://sourcedaddy.com/ms-excel/make-formulas-increment-rows.html

However I was unable to adapt it to handle pulling from a different sheet and splitting the cell reference, since I wanted the Columns to stay as is.

I also saw this user's question: Incrementally autofill a formula with a cell reference from another sheet? (=INDIRECT?)

But the answer is just "do this" so I'm struggling to make sense of the process involved. I don't mind being given the answer however I would appreciate a breakdown of the process so I can have a better understanding.

Many thanks.

CodePudding user response：

INDEX When Incrementing Rows By Columns

In cell B2:

=IF((MOD(($A2 (52-INDEX(Sheet2!$E:$E,COLUMN()))),INDEX(Sheet2!$D:$D,COLUMN()))=0),1,0)*INDEX(Sheet2!$C:$C,COLUMN())

• INDEX($E:$E,2) means $E$2; INDEX($E:$E,3) means $E$3;...
• In column B, COLUMN() means 2; in column C, COLUMN() means 3; ...
• In column B, INDEX($E:$E,COLUMN()) means $E$2; in column C, INDEX($E:$E,COLUMN()) means $E$3;...