Help setting a large amount of cells to Absolute Reference

[MattP817]

I have three columns with 4380 cells each that I need to change them to absolute references.

Long story short. This is a database that is fed from Power Automate forms. Every time a new row is added into the "ShiftData" table on the "Data" sheet my formatting gets messed up. I have an additional sheet that is pulling the calculations, and than I reference that cell on my "Data" sheet to bring in the calculation. I have everything working correctly, now I just need to make them absolute so that the correct data carries over and the cell references do not change. 

I was trying to setup these formulas into the data sheet where PowerAutomate drops the data, but it wouldn't maintain the formulas. I thought setting up a separate sheet that grabbed the data and completed the formula, I could reference those cells on the data sheet.

A4, A7, A10, A13 - B4, B7, B10, B13 - C4, C7, C10, C13 are all setup correctly. I dragged the formulas down, but when new data is entered the formulas end up changing. I am thinking that if I switch all of the formulas to absolute, It should work smoothly...I hope.

I have tried macros that I have found on threads, but I cannot get them to work correctly. PLEASE HELP :)

[#2 · Accepted solution]

@MattP817 

=IF(INDIRECT("'"&$J$1&"'!B"&(MOD(ROW(E2),12))+2)="third",SUM(INDIRECT("'"&$J$1&"'!V"&(MOD(ROW(E2),12))&":V"&MOD(ROW(E2),12)+2),INDIRECT("'"&$J$1&"'!AV"&(MOD(ROW(E2),12))&":AV"&MOD(ROW(E2),12)+2),INDIRECT("'"&$J$1&"'!BX"&(MOD(ROW(E2),12))&":BX"&MOD(ROW(E2),12)+2),INDIRECT("'"&$J$1&"'!CX"&(MOD(ROW(E2),12))&":CX"&MOD(ROW(E2),12)+2),),0)

Maybe with the above formula.

[#3]

That worked for column A and B! Thank you! This is way above my Excel knowledge base.
I still need help on column C. I have an extra division step in that column and I cannot figure out how to implement it into the new formula.
Can you take a look at that one for me?

[#4]

@MattP817 

=IF(INDIRECT("'"&$J$1&"'!B"&(MOD(ROW(E2),12))+2)="third",SUM(INDIRECT("'"&$J$1&"'!V"&(MOD(ROW(E2),12))&":V"&MOD(ROW(E2),12)+2))/TechnologyTmax!$B$2+SUM(INDIRECT("'"&$J$1&"'!AV"&(MOD(ROW(E2),12))&":AV"&MOD(ROW(E2),12)+2))/TechnologyTmax!$B$3+SUM(INDIRECT("'"&$J$1&"'!BX"&(MOD(ROW(E2),12))&":BX"&MOD(ROW(E2),12)+2))/TechnologyTmax!$B$4+SUM(INDIRECT("'"&$J$1&"'!CX"&(MOD(ROW(E2),12))&":CX"&MOD(ROW(E2),12)+2))/TechnologyTmax!$B$5,0)

Maybe with this formula.

[#5]

You Sir, are a gentleman and a scholar. I appreciate your help!
Signing up for some extra Excel classes this semester. I need to be able to understand more of these complex formulas.