Hi there,
The sample below returns the correct results on row 2, though it is only because I have manually amended the cell ref in the WORKINGS! worksheet, because when a copy the formula form left to right it naturally goes WORKINGS!A9, WORKINGS!B9, WORKINGS!C9....and so on. When I actually want it to freeze the column A when copying the formula.
Any assistance would be appreciated.
Regards
Ben
The sample below returns the correct results on row 2, though it is only because I have manually amended the cell ref in the WORKINGS! worksheet, because when a copy the formula form left to right it naturally goes WORKINGS!A9, WORKINGS!B9, WORKINGS!C9....and so on. When I actually want it to freeze the column A when copying the formula.
Any assistance would be appreciated.
Regards
Ben
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1:G1 | B1 | =A1+1 |
A2 | A2 | =IF('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!A9="ACTUAL EXPENDITURE",INDEX('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!$G$9:$G$35,ROUNDUP(COLUMNS($A2:A2),0)),0) |
B2 | B2 | =IF('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!A10="ACTUAL EXPENDITURE",INDEX('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!$G$9:$G$35,ROUNDUP(COLUMNS($A2:B2),0)),0) |
C2 | C2 | =IF('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!A11="ACTUAL EXPENDITURE",INDEX('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!$G$9:$G$35,ROUNDUP(COLUMNS($A2:C2),0)),0) |
D2 | D2 | =IF('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!A12="ACTUAL EXPENDITURE",INDEX('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!$G$9:$G$35,ROUNDUP(COLUMNS($A2:D2),0)),0) |
E2 | E2 | =IF('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!A13="ACTUAL EXPENDITURE",INDEX('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!$G$9:$G$35,ROUNDUP(COLUMNS($A2:E2),0)),0) |
F2 | F2 | =IF('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!A14="ACTUAL EXPENDITURE",INDEX('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!$G$9:$G$35,ROUNDUP(COLUMNS($A2:F2),0)),0) |
G2 | G2 | =IF('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!A15="ACTUAL EXPENDITURE",INDEX('[MASTER CONTROL DOC 2020.xlsm]WORKINGS'!$G$9:$G$35,ROUNDUP(COLUMNS($A2:G2),0)),0) |