Pivot Table GETPIVOTDATA


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?
 
Ad

Advertisements

E

Eva

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
 
Ad

Advertisements

E

Eva

and this is when you want the total for all divisions
GETPIVOTDATA("Amount",$A$1,"Month",DATE(2005,1,2))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top