# GetPivotTable Data question

#### John P

##### Board Regular
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.

##### MrExcel MVP
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
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.

##### MrExcel MVP
Look at the example - for the total for AA , you just use "AA", not "AA Total".

#### John P

##### Board Regular

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.

##### MrExcel MVP
describe your pivot table set up in more detail.

#### John P

##### Board Regular
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
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.

Replies
0
Views
392
Replies
0
Views
337
Replies
1
Views
127
Replies
17
Views
1K
Replies
5
Views
350

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?

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