Having trouble getting Pivot Table data to pull from pivot table to regular table using =GetPivotData()

[dragontbone]

I am trying to pull data from the pivot table into the table at the top of the page. I keep getting an #REF error and am unsure what is happening.

CurrentCommissionTracker.xlsm
	A	B	C	D	E	F	G	H
5		Michael	Matt	Grant	Vicki	Shawna/John	Total	Average
6
7	Num of Transactions	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
8	Total Transaction Amt	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
9	Gross Commissions	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
10	% WP Affiliated	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
11	% Non-Affiliated	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
12	Detail by Deal Type
13
14	Ave Transaction Gross	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
15	Ave Transaction size	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
16	Broker Commissions	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
17	Corporate Commissions	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
18	Share to Broker	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
19
20
21
22
23
24
25
26	Year	(All)
27	Comm Status	(All)
28
29		Column Labels
30	Values	Grant	John	Matt	Michael	Shawna	Vicki	Grand Total
31	Count of Invoice #	62	18	24	26	18	42	190
32	Sum of Sale Price	$13,476,424.12	$3,057,795.43	$8,928,688.87	$1,494,484.13	$3,237,795.43	$6,720,730.23	$36,915,918.21
33	Sum of Gross	$ 500,282.43	$ 111,181.19	$ 104,052.43	$ 76,094.50	$ 120,181.19	$ 267,308.78	$ 1,179,100.52
34	Average of Gross	$ 8,069.07	$ 6,540.07	$ 4,524.02	$ 3,043.78	$ 7,069.48	$ 6,519.73	$ 6,373.52
35	Sum of Broker Commission	$198,247.95	$50,699.24	$51,929.08	$34,273.58	$56,099.24	$89,670.04	$480,919.13
36	Sum of Corporate	$198,247.94	$33,799.52	$51,929.10	$34,273.60	$37,399.52	$89,670.01	$445,319.69
37
38
39
40
41
42
43
44
45
46
47
48
49	Year	(All)
50	Comm Status	(All)
51
52	Sum of Sale Price	Column Labels
53	Row Labels	Grant	John	Matt	Michael	Shawna	Vicki	Grand Total
54	Consulting	$ 8,500.00			$ -		$ 87,563.88	$ 96,063.88
55	Lease Expansion		$ 94,332.06	$ 88,701.31		$ 94,332.06		$ 277,365.43
56	Lease Renewal	$ 2,423,004.25	$ 720,459.54	#############	$ 628,118.89	$ 720,459.54	#############	##############
57	New Lease	$ 3,771,059.87	$ 396,739.83	#############	$ 691,365.24	$ 396,739.83	#############	##############
58	Sale	$ 7,273,860.00	#############		$ 175,000.00	#############	#############	##############
59	Grand Total	13476424.12	3057795.43	8928688.87	1494484.13	3237795.43	6720730.23	36915918.21
60
61
62
63
64
65
66
67	Comm Status	(All)
68	Year	(All)
69	WP Affiliate	Yes
70
71		Column Labels
72		Grant	John	Matt	Michael	Shawna	Vicki	Grand Total
73	Count of Invoice #	13	1	13	1	1	11	40
OBK Summary Detail

Cell Formulas
Range	Formula
B7	B7	=GETPIVOTDATA(GETPIVOTDATA("Count of Invoice #",$A$29,"Broker","Michael"),Net_Acc_Comm,"Count of Invoice #",$A$29,"Broker","Michael")
C7	C7	=GETPIVOTDATA("[Measures].[Count of Invoice #]",$A$29,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Matt]")
D7	D7	=GETPIVOTDATA("[Measures].[Count of Invoice #]",$A$29,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Grant]")
E7	E7	=GETPIVOTDATA("[Measures].[Count of Invoice #]",$A$29,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Vicki]")
F7	F7	=(GETPIVOTDATA("[Measures].[Count of Invoice #]",$A$29,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[John]")+GETPIVOTDATA("[Measures].[Count of Invoice #]",$A$29,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Shawna]"))/2
G7:G9	G7	=SUM(B7:F7)
H7:H9	H7	=G7/7
B8	B8	=GETPIVOTDATA("[Measures].[Sum of Sale Price]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Michael]")
C8	C8	=GETPIVOTDATA("[Measures].[Sum of Sale Price]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Matt]")
D8	D8	=GETPIVOTDATA("[Measures].[Sum of Sale Price]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Grant]")
E8	E8	=GETPIVOTDATA("[Measures].[Sum of Sale Price]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Vicki]")
F8	F8	=GETPIVOTDATA("[Measures].[Sum of Sale Price]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[John]")+GETPIVOTDATA("[Measures].[Sum of Sale Price]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Shawna]")
B9	B9	=GETPIVOTDATA("[Measures].[Sum of Gross]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Michael]")
C9	C9	=GETPIVOTDATA("[Measures].[Sum of Gross]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Matt]")
D9	D9	=GETPIVOTDATA("[Measures].[Sum of Gross]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Grant]")
E9	E9	=GETPIVOTDATA("[Measures].[Sum of Gross]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Vicki]")
F9	F9	=GETPIVOTDATA("[Measures].[Sum of Gross]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[John]")+GETPIVOTDATA("[Measures].[Sum of Gross]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Shawna]")
B10	B10	=GETPIVOTDATA("[Measures].[Distinct Count of Invoice #]",$A$75,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Michael]")/B7
C10	C10	=GETPIVOTDATA("[Measures].[Distinct Count of Invoice #]",$A$75,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Matt]")/C7
D10	D10	=GETPIVOTDATA("[Measures].[Distinct Count of Invoice #]",$A$75,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Grant]")/D7
E10	E10	=GETPIVOTDATA("[Measures].[Distinct Count of Invoice #]",$A$75,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Vicki]")/E7
F10	F10	=GETPIVOTDATA("[Measures].[Distinct Count of Invoice #]",$A$75,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Shawna]")/F7
G10	G10	=GETPIVOTDATA("[Measures].[Distinct Count of Invoice #]",$A$75)/G7
B11	B11	=(B7-GETPIVOTDATA("[Measures].[Distinct Count of Invoice #]",$A$75,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Michael]"))/B7
C11	C11	=(C7-GETPIVOTDATA("[Measures].[Distinct Count of Invoice #]",$A$75,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Matt]"))/C7
D11	D11	=(D7-GETPIVOTDATA("[Measures].[Distinct Count of Invoice #]",$A$75,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Grant]"))/D7
E11	E11	=(E7-GETPIVOTDATA("[Measures].[Distinct Count of Invoice #]",$A$75,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Vicki]"))/E7
F11	F11	=(F7-GETPIVOTDATA("[Measures].[Distinct Count of Invoice #]",$A$75,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Shawna]"))/F7
G11	G11	=(G7-GETPIVOTDATA("[Measures].[Distinct Count of Invoice #]",$A$75))/G7
B14	B14	=GETPIVOTDATA("[Measures].[Average of Gross]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Michael]")
C14	C14	=GETPIVOTDATA("[Measures].[Average of Gross]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Matt]")
D14	D14	=GETPIVOTDATA("[Measures].[Average of Gross]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Grant]")
E14	E14	=GETPIVOTDATA("[Measures].[Average of Gross]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Vicki]")
F14	F14	=GETPIVOTDATA("[Measures].[Average of Gross]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Shawna]")+GETPIVOTDATA("[Measures].[Average of Gross]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[John]")
G14	G14	=GETPIVOTDATA("[Measures].[Average of Gross]",#REF!)
B15	B15	=GETPIVOTDATA("[Measures].[Average of Square Ft]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Michael]")
C15	C15	=GETPIVOTDATA("[Measures].[Average of Square Ft]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Matt]")
D15	D15	=GETPIVOTDATA("[Measures].[Average of Square Ft]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Grant]")
E15	E15	=GETPIVOTDATA("[Measures].[Average of Square Ft]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Vicki]")
F15	F15	=GETPIVOTDATA("[Measures].[Average of Square Ft]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[John]")+GETPIVOTDATA("[Measures].[Average of Square Ft]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Shawna]")
G15	G15	=GETPIVOTDATA("[Measures].[Average of Square Ft]",#REF!)
B16	B16	=GETPIVOTDATA("[Measures].[Sum of Broker Commission]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Michael]")
C16	C16	=GETPIVOTDATA("[Measures].[Sum of Broker Commission]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Matt]")
D16	D16	=GETPIVOTDATA("[Measures].[Sum of Broker Commission]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Grant]")
E16	E16	=GETPIVOTDATA("[Measures].[Sum of Broker Commission]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Vicki]")
F16	F16	=GETPIVOTDATA("[Measures].[Sum of Broker Commission]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Shawna]")+GETPIVOTDATA("[Measures].[Sum of Broker Commission]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[John]")
G16	G16	=GETPIVOTDATA("[Measures].[Sum of Broker Commission]",#REF!)
B17	B17	=GETPIVOTDATA("[Measures].[Sum of Corporate]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Michael]")
C17	C17	=GETPIVOTDATA("[Measures].[Sum of Corporate]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Matt]")
D17	D17	=GETPIVOTDATA("[Measures].[Sum of Corporate]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Grant]")
E17	E17	=GETPIVOTDATA("[Measures].[Sum of Corporate]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Vicki]")
F17	F17	=GETPIVOTDATA("[Measures].[Sum of Corporate]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[John]")+GETPIVOTDATA("[Measures].[Sum of Corporate]",#REF!,"[Net_Acc_Comm].[Broker]","[Net_Acc_Comm].[Broker].&[Shawna]")
G17	G17	=GETPIVOTDATA("[Measures].[Sum of Corporate]",#REF!)
B18:G18	B18	=B16/(B16+B17)

 

[dragontbone]

This has been fixed. I needed to watch a video on how the function actually worked.

 

[smozgur]

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. I understand you shared a video link but it is a 12 minutes video, so that would be more helpful if you could explain what was wrong in your data set.
Otherwise, please do not mark a post that doesn't contain a solution.

 

[dragontbone]

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. I understand you shared a video link but it is a 12 minutes video, so that would be more helpful if you could explain what was wrong in your data set.
Otherwise, please do not mark a post that doesn't contain a solution.

I understand. The solution was to watch the video to understand the formula. My lack of knowledge about the formula was what the problem was. At the time, I didn't think to look for a video of the function, so I came here thinking that this was the best place for help. The solution is to do some research before coming here for help.

 

[smozgur]

You posted a mini-sheet with #REF! errors above, because you wrote a wrong formula, or deleted a referenced column or columns.
Apparently, after watching the video, and making some research, you found the solution. That’s great!

Now, you can just copy another mini-sheet with the fixed formula and post it. That will show the solution to the future readers of this question that might be having the similar problem.

If you can’t do that for some reason, please do not mark a solution that doesn’t contain a solution.

I hope this makes sense.