GetPivotTable Data question

John P

Board Regular
Joined
Oct 17, 2002
Messages
108
I had the entire post drafted and then somehow deleted it, so this will be brief. I have no problems using this function when sourcing the normal fields in a pivot table, but it I attempt to source the "automated summation" fields, I receive the #REF error. Is there a way to successfully source these values as opposed to summing all the GetPivotTable data values. I need a more efficient process because the current process forces me to validate the presense of the data with the =(IF(ISNUMBER(MATCH

The problem with using the ISNUMBER is that I hit the Excel limit for characters in a cell and it does not work. Any suggestions are appreciated. Thanks
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
not sure what you mean - the totals / subtotals can be returned in a getpivotdata():
Book2
ABCDEFG
1ID1ID2Num
2AAa1
3ABa2SumofNum
4AAa3ID1ID2Total
5ABb4AAa4
6AAb5b5
7ABb6AATotal9
8ABa2
9b10
10ABTotal12
11GrandTotal21
12
13Grandtotal21
14ID1AAtotal9
15
Sheet1
 

John P

Board Regular
Joined
Oct 17, 2002
Messages
108
It works if I reference the grand total, but not any of the higher level totals like AA Total in your example. Does your work if you reference AA Total? If I could figure out how to post a picture of what I am working with I would do it.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Look at the example - for the total for AA , you just use "AA", not "AA Total".
 

John P

Board Regular
Joined
Oct 17, 2002
Messages
108

ADVERTISEMENT

I follow your logic, but it does not seem to work. Could it be because I have one more piece of logic than you? I need to drive it to the month, so I need PA Completed for the specific reporting month. My formula looks like =GETPIVOTDATA(F7,"Reporting Month","12/1/2003","PA") and it returns the #REF error.
 

John P

Board Regular
Joined
Oct 17, 2002
Messages
108
The layout of my table is as follows:
Page: Region & Term_Type
ROW: Derived Type & Market
Column: Reporting Month
Data: Completed, Compliant, Compliance %, Late-Delinquent & Net Compliance.

I am attempting to retuinr the Derived_Type Summation value for a particular reporting month.

Please let me know if you require further detail. Thanks!!
 

John P

Board Regular
Joined
Oct 17, 2002
Messages
108
After all of these questions, I just tried to "dummy" method and linked to the cell in the pivot. Here is the formula that worked:
=GETPIVOTDATA("Completed ",$F$6,"Reporting Month",DATE(2003,12,1),"Derived Type","PA")

Thanks for working with me on this.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,683
Messages
5,637,773
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top