Log In
Register
Home
New Member
April 24, 2019
Offline
I need to use GETPIVOTDATA to get different data points where I need one of the fields to be a dynamic reference. I've done this before where the source of data is a table within the same workbook, but this is the first time I'm trying with a connection to an external source. I have tried different things to reference the dynamic information like TEXT(), T(), INDIRECT() but still get errors when trying to change one of the fixed fields to a cell reference. For example, in the following formula, I'd like to be able to change North America to be different things depending on a cell reference, let's say B1=Latin America
=GETPIVOTDATA("[Measures].[Unit Qty]",'a) EGI '!$A$9,"[WW Detailed Report].[CATEGORY REFERENCE]","[WW Detailed Report].[CATEGORY REFERENCE].&[Rack]","[WW Detailed Report].[REV HEADER]","[WW Detailed Report].[REV HEADER].&[Revenue]","[WW Detailed Report].[GEO]","[WW Detailed Report].[GEO].&[North America]")
Thanks
See Answer
July 16, 2010
Offline
Hi Enrique,
I don't think you can use GETPIVOTDATA on an external Power Pivot model. I know with regular PivotTables the workbook being referenced must be open for GETPIVOTDATA to work. You could try that, if it doesn't make any difference then I'd say it's a case of my first suspision.
Mynda
Trusted Members
Moderators
November 1, 2018
Offline
Assuming the cell A1 just contains North America, or Latin America, you can use:
=GETPIVOTDATA("[Measures].[Unit Qty]",'a) EGI '!$A$9,"[WW Detailed Report].[CATEGORY REFERENCE]","[WW Detailed Report].[CATEGORY REFERENCE].&[Rack]","[WW Detailed Report].[REV HEADER]","[WW Detailed Report].[REV HEADER].&[Revenue]","[WW Detailed Report].[GEO]","[WW Detailed Report].[GEO].&["&A1&"]")
Answers Post
1 Guest(s)
