Pivot Table GETPIVOTDATA

  • Thread starter Thread starter Buck
  • Start date Start date
B

Buck

I have a simple data table which I am using to better understand GETPIVOTDATA.
PivotTable1 contains only 12 rows of data like so:
Month Division Acct Amount
1/1/2005 A 100 553
1/1/2005 A 200 714
1/1/2005 A 300 6
1/1/2005 B 100 430
1/1/2005 B 200 729
1/1/2005 B 300 246
2/1/2005 A 100 670
2/1/2005 A 200 64
2/1/2005 A 300 799
2/1/2005 B 100 948
2/1/2005 B 200 563
2/1/2005 B 300 884

I have tried several variations of GETPIVOTDATA, but I am stuck (#REF!) on
two in particular. The simple variations work well:
=GETPIVOTDATA("Amount",$A$3)
=GETPIVOTDATA($A$3,"Grand Total")
=GETPIVOTDATA($A$3,"Amount")
=GETPIVOTDATA($A$3,"Sum of Amount")

But these two return #REF!:
=GETPIVOTDATA("Amount", $A$3, "Month", "2/1/2005", "Division",
"A")=GETPIVOTDATA($A$3,"2/1/2005 A")

How can I get these to work?
 
You should say which division is it:
=GETPIVOTDATA("Amount",$H$1,"Month",DATE(2005,1,2),"Division","A")
and your pivot should looks like that:
Sum of Amount
Division Month Total
A 01/01/2005 1273
02/01/2005 1533
A Total 2806
B 01/01/2005 1405
02/01/2005 2395
B Total 3800
Grand Total 6606

You can get the error message if you don't have some part of the information
you are looking for there
Click yes if helped
 
and this is when you want the total for all divisions
GETPIVOTDATA("Amount",$A$1,"Month",DATE(2005,1,2))
 
Back
Top