|
getpivotdata function
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. |
getpivotdata function
What version of Excel are you using?
What's the formula that works when you hae only one data field? MarkM wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
getpivotdata function
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. "Debra Dalgleish" wrote: What version of Excel are you using? What's the formula that works when you hae only one data field? MarkM wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
getpivotdata function
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" wrote: What version of Excel are you using? What's the formula that works when you hae only one data field? MarkM wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
getpivotdata function
Great! Thanks for posting your solution.
MarkM wrote: 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" wrote: What version of Excel are you using? What's the formula that works when you hae only one data field? MarkM wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| All times are GMT +1. The time now is 05:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com