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
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
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&"]")
Thank you Velouria, I can't believe I didn't try this before. It did work!
