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

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
245
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)
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top