INDEX (copying formula when translating from columns to row presentation)

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
234
Office Version
  1. 365
Platform
  1. Windows
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

Cell Formulas
RangeFormula
B1:G1B1=A1+1
A2A2=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)
B2B2=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)
C2C2=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)
D2D2=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)
E2E2=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)
F2F2=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)
G2G2=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)
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,561
Office Version
  1. 365
Platform
  1. Windows
I'm curious as to why you haven't just used the same INDEX method in that part of your formula that you have used with the G9:G35 range. Although I suspect that you could do what you need to with a much simpler formula, perhaps SUMIFS, but without knowing what else is in the WORKINGS sheet which might be of use there is very little to go on.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,695
Messages
5,573,673
Members
412,548
Latest member
georgepre10
Top