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,188
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,188
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,188
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Can you post a workbook somewhere for testing?
 

Forum statistics

Threads
1,078,464
Messages
5,340,462
Members
399,376
Latest member
Tresfjording

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top