Getpivotdata

[gavs73]

Hi

Would appreciate any help with this GETPIVOTDATA, I need to make it dynamic, and link &[2.018E3] to a year in a cell and&[1.] to week in a cell. I've tried a few things but nothing is working.

=GETPIVOTDATA("[Measures].[MP Count]",'[3Data.xlsx]ATT ENT'!$A$4,"[Subscriptions].[Attend Type]","[Subscriptions].[Attend Type].&[3MONTH]","[Subscriptions].[Trans Type]","[Subscriptions].[Trans Type].&[ATT]","[Subscriptions].[Start Fiscal Year]","[Subscriptions].[Start Fiscal Year].&[2.018E3]","[Subscriptions].[Start Fiscal Week]","[Subscriptions].[Start Fiscal Week].&[1.]")


Thanks for your help.

Gavin

 

[RoryA]

What exactly is in the two cells, and what did you try that didn't work?

 

[gavs73]

Hi Rory, C2 = 2018 and C1 = 1, I've also tried '2018 and '1 and 2.018E3
I've tried "[Subscriptions].[Start Fiscal Year].&["&C2&"]" , "[Subscriptions].[Start Fiscal Year].&"&C2 , "[Subscriptions].[Start Fiscal Year]","[Subscriptions].[Start Fiscal Year].["+C2&"]"
Thanks
Gavin

 

[RoryA]

Does this work:

=GETPIVOTDATA("[Measures].[MP Count]",'[3Data.xlsx]ATT ENT'!$A$4,"[Subscriptions].[Attend Type]","[Subscriptions].[Attend Type].&[3MONTH]","[Subscriptions].[Trans Type]","[Subscriptions].[Trans Type].&[ATT]","[Subscriptions].[Start Fiscal Year]","[Subscriptions].[Start Fiscal Year].&["&C2&"]","[Subscriptions].[Start Fiscal Week]","[Subscriptions].[Start Fiscal Week].&["&C1&".]")

or perhaps:

=GETPIVOTDATA("[Measures].[MP Count]",'[3Data.xlsx]ATT ENT'!$A$4,"[Subscriptions].[Attend Type]","[Subscriptions].[Attend Type].&[3MONTH]","[Subscriptions].[Trans Type]","[Subscriptions].[Trans Type].&[ATT]","[Subscriptions].[Start Fiscal Year]","[Subscriptions].[Start Fiscal Year].&["&SUBSTITUTE(TEXT(C2,"0.000E+00"),"+","")&"]","[Subscriptions].[Start Fiscal Week]","[Subscriptions].[Start Fiscal Week].&["&C1&".]")

 

[gavs73]

Sadly not, but I appreciate you trying for me.

 

[RoryA]

Can you post a workbook somewhere for testing?