How do I increment a different sheet cell reference row as i autofill across columns?

Question

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.