GetPivotData (Pivot Table vs Power Pivot Table) behavior

Background information:

• Dashboard is in Excel 2010 using linked data connections to SQL server tables/views
• This is working perfectly and is easy to update using the getpivotdata function from pivot tables. I update 1 field and all the supplemental graphs and tables are updated correctly
• I'm contemplating migrating the dashboard to utilizing Power Pivot Tables as a revision because of slicers, sheer number of records and data set portability as well as additional data requests from my users

What I have noticed is that the getpivotdata syntax is different for Power Pivot and "normal" pivot tables. I have created a sample document with the two behaviors.

on pivot tables the syntax is =+GETPIVOTDATA("Sales",$A$15,"Store",1,"Period",4) and I am able to replace any of the measures with cell references. For example: =+GETPIVOTDATA("Sales",$A$15,"Store",1,"Period",$A$25)

on pivot tables created using power pivot data the syntax is =+GETPIVOTDATA("[Measures].[Sum of Sales]",$A$29,"[Table1].[Store]","[Table1].[Store].&[1]","[Table1].[Period]","[Table1].[Period].&[4]")

What I am trying to figure out is how the formula needs to be written to allow the functionality I experienced with the "normal" pivot tables

Thank you in advance for you time.

Charles Banks

Thanks to all that looked into this. I have found the solution. For those of you curious to create a floating reference for powerpivot pivot tables the syntax is as follows:

=+GETPIVOTDATA("[Measures].[Sum of Sales]",$A$29,"[Table1].[Store]","[Table1].[Store].&[1]","[Table1].[Period]","[Table1].[Period].&["&A45&"]")

This applies to the above example.

Charles,

Sorry I didn't get here in time to help you through this. In addition, you can also use Excel's Cube formulas against PowerPivot data as well. The advantage of this is that you don't need the PivotTable sitting in a worksheet if all you want is one value from it.

Ken -
Thanks for the information. I'll have to look into cube functions. Is there any performance hit?