GETPIVOTDATA Formula Error - Using Named References

[Mark Bit]

Good afternoon all, hope everyone is well.

I am stuck with a GetPivot data formula. I constructed a basic sample of what I am dealing with so you can check it out. Due to the way the data is stored I cannot simply use a SumIF, which is monumentally easier. But anyway, I have to construct something with what I have - which is create a Pivot Table to look how I need it on order to extract data even further.

The sales works fine using SumIf

But I keep getting a #REF when I try to retrieve Picot data using named references. Of course it would be easy for me to just click "=" and then click the data field I want, however, I am dealing with a LOT more data than this sample set. Not to mention I have to extend my summary page to include 5 years worth - which will be referencing 5 different pivot tables.

Any idea where my most likely stupid mistake is occurring??:

 

[Luke M]

Your current formula:
=GETPIVOTDATA("Total $",PT2011,"Account",$B6,"Month","Jan")

If you start to type a formula, and click on PT, you'll see that syntax should be more like:
=GETPIVOTDATA("[Measures].[Sum of Total $]",Pivot!$B$2,"[Table1].[Month]","Mar","[Table1].[Account]","Paul")
we can see that your field names are not correct.

Also, while "PT2011", is the name you gave the PT, it's not a valid named range. If you go to Formulas - Named Range Manager, you can verify that it doesn't show up.

Proper ammended formula:
=GETPIVOTDATA("[Measures].[Sum of Total $]",Pivot!$B$2,"[Table1].[Month]","Jan","[Table1].[Account]",$B6)