Jan 13 2022 06:52 AM
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 :)
Jan 13 2022 08:13 AM
Solution=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.
Jan 13 2022 10:12 AM
Jan 14 2022 05:25 AM
=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.
Jan 14 2022 05:39 AM
Jan 13 2022 08:13 AM
Solution=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.