Working on a file with 2 tabs where I need to use index to reference a value in the pivot table.
I have a pivot table with months down the rows and item numbers across the columns. The Item number is not necessarily in the same column location in my data tab as it is in the pivot table
Pivot Tab
ABC CDE
Jan
Feb
Mar
Apr
May
Data Tab
ABC DEF
Jan
Feb
Mar
Apr
May
I am using the following formula to perform my calculation
='Data'!B6/INDEX('Pivot'!B$8:B$19,MONTH('Data'!$A6))
This gives me the sales for that month divided by the index value generated for that particular month.
When I copy this across at one point my columns are no longer in sync and I am getting the wrong answer.
How can I make the index lock onto the correct column based on the Item number?
I have a pivot table with months down the rows and item numbers across the columns. The Item number is not necessarily in the same column location in my data tab as it is in the pivot table
Pivot Tab
ABC CDE
Jan
Feb
Mar
Apr
May
Data Tab
ABC DEF
Jan
Feb
Mar
Apr
May
I am using the following formula to perform my calculation
='Data'!B6/INDEX('Pivot'!B$8:B$19,MONTH('Data'!$A6))
This gives me the sales for that month divided by the index value generated for that particular month.
When I copy this across at one point my columns are no longer in sync and I am getting the wrong answer.
How can I make the index lock onto the correct column based on the Item number?