GetPivotTable Data question

[John P]

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

 

[PaddyD]

not sure what you mean - the totals / subtotals can be returned in a getpivotdata():

Book2
	A	B	C	D	E	F	G
1	ID1	ID2	Num
2	AA	a	1
3	AB	a	2		SumofNum
4	AA	a	3		ID1	ID2	Total
5	AB	b	4		AA	a	4
6	AA	b	5			b	5
7	AB	b	6		AATotal		9
8					AB	a	2
9						b	10
10					ABTotal		12
11					GrandTotal		21
12
13				Grandtotal	21
14				ID1AAtotal	9
15
Sheet1

 

[John P]

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]

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

 

[John P]

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.

 

[PaddyD]

describe your pivot table set up in more detail.

 

[John P]

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]

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.