How to automate getpivotdata formula?

[iambadatexcel]

Hi all, I am new to the forum and am hoping that I could get some help!

I am trying to pull data out of a pivot table, and below is my formula.

=GETPIVOTDATA("[Measures].[Occ]",'BI-Occ'!$A$5,"[Market].[Market Code]","[Market].[Market Code].&[BAR]","[Dates].[Day]","[Dates].[Day].&[3]","[Dates].[Month]","[Dates].[Month].&[1]","[Dates].[Year]","[Dates].[Year].&[17]")

The problem I am having is that I am unable to drag the cell as it returns the same value. I need to change the numbers in here "[Dates].[Day].&[3]" such that I am able to have the number 1 2 3 4 5 and so forth in the brackets. Is there any kind soul who could help me figure out? ><

Thank you in advance!

 

[dwilliamson1024]

Is there a cell you can reference instead of "[Dates].[Day].&[3]"? For instance if the 1,2,3,4,5 are in column A in the pivot, change the getpivotdata function to say $A1 to reference that cell instead...and when you drag the formula it will adjust the $A1 to $A2, $A3, etc. to pull the value of the number instead of the location.

 

[RoryA]

To start from 1:

=GETPIVOTDATA("[Measures].[Occ]",'BI-Occ'!$A$5,"[Market].[Market Code]","[Market].[Market Code].&[BAR]","[Dates].[Day]","[Dates].[Day].&["&ROWS($A$1:$A1)&"]","[Dates].[Month]","[Dates].[Month].&[1]","[Dates].[Year]","[Dates].[Year].&[17]")

which will increment as you copy it down.

 

[iambadatexcel]

Hi Rory, it works perfectly! Thank you so so much!!!

 

[RoryA]

Glad to help.