Forum Discussion

Copper Contributor

Jan 13, 2022

Solved

Help setting a large amount of cells to Absolute Reference

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 ad...

JacksonBlendingShiftReportFinal.xlsm22.2 MB

excel

Formulas and Functions

OliverScheurich
Jan 13, 2022
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.
JacksonBlendingShiftReportFinal.xlsm22.2 MB

OliverScheurich

Gold Contributor

Jan 13, 2022

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.

JacksonBlendingShiftReportFinal.xlsm22.2 MB

MattP817
Copper Contributor
Jan 13, 2022
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?
- OliverScheurich
  Gold Contributor
  Jan 14, 2022
  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.
  JacksonBlendingShiftReportFinal.xlsm22.2 MB
  - MattP817
    Copper Contributor
    Jan 14, 2022
    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.