getpivotdata function

[MarkM]

In Excel 2000, can I have more than one data field in my pivot table and
still use the GETPIVOTDATA function?

My pivot table is laid out as such:
Date
DesShop Pathway Data 200501 200502
240 Database Sum of DeliveryQty 12 14
Sum of DeliveryAmt 15853 15652
Retail Sum of DeliveryQty 3 4
Sum of DeliveryAmt 2997 7888
Grassroots Sum of DeliveryQty 0
Sum of DeliveryAmt 0
Managed Care Sum of DeliveryQty 1
Sum of DeliveryAmt 1995
240 St. Peterburg Sum of DeliveryQty 16 18
240 St. Peterburg Sum of DeliveryAmt 20845 23540

If I only have the Sum of DeliveryAmt data field in my table the
GETPIVOTDATA function works. But I cannot figure out how to get it to work
with both data fields on my table. I have tried changing the Pivot_table to
reference the cell number (C8) and also tried to enter in the data field name
(Sum of DeliveryAmt) but can not get it to work.

Thanks in advance.

 

[Debra Dalgleish]

What version of Excel are you using?
What's the formula that works when you hae only one data field?

 

[MarkM]

I am in Excel 2000.

If I have my table set up with only one data field (DeliveryAmt) the
GETPIVOTDATA formula =GETPIVOTDATA(A5,"240 St. Peterburg Database 200501")
works, it gets 15853.

Sum of DeliveryAmt Date
DesShop Pathway 200501
240 St. Peterburg Database 15853
Retail 2997
Grassroots 0
Managed Care 1995
240 St. Peterburg Total 20845

Cell A5 has the data field Sum of DeliveryAmt. When I add another data
field, sum of DeliveryQty it then puts the two data fields in column C and A5
is blank, see example from original post. I change A5 to C8, which is the
cell that the DeliveryAmt is located in but the formula does not work, I get
a #REF message. I have tried entering the field name DeliveryAmt, Sum of
DeliveryAmt and get a #NAME message.

Thanks for helping with this.

 

[MarkM]

Debra, Thanks for your help. I figurded out what I needed to do. I created
a named range for the entire pivot table and used that for the reference
field. I was also missing the Sum of DeliveryAmt in the name range. So my
function looks like this: =GETPIVOTDATA(PivotTable,"'240 St. Peterburg'
'Database' '200501' 'Sum of DeliveryAmt'")

 

[Debra Dalgleish]

Great! Thanks for posting your solution.

Debra, Thanks for your help. I figurded out what I needed to do. I created
a named range for the entire pivot table and used that for the reference
field. I was also missing the Sum of DeliveryAmt in the name range. So my
function looks like this: =GETPIVOTDATA(PivotTable,"'240 St. Peterburg'
'Database' '200501' 'Sum of DeliveryAmt'")


: