Forum Discussion
Help setting a large amount of cells to Absolute Reference
=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.
=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.
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?
=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.
- 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.