Getpivotdata

gavs73

Board Regular
Joined
Apr 22, 2008
Messages
102
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,151
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
What exactly is in the two cells, and what did you try that didn't work?
 

gavs73

Board Regular
Joined
Apr 22, 2008
Messages
102
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,151
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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&".]")
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,151
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Can you post a workbook somewhere for testing?
 

Forum statistics

Threads
1,077,919
Messages
5,337,183
Members
399,131
Latest member
Vinnyjuice

Some videos you may like

This Week's Hot Topics

Top