Opposite Pivot Table Filter in 2nd Pivot Table

jamesrr25 · Apr 10, 2016

Hello,

I am attempting to create a "peer" pivot table = PVT2(H5), based off the filter selection in PVT1(cell E5). My plan is to use a combo box to have the user select a school to review in PVT1, and by doing so they could view that school's peers(all other schools except the one selected) in PVT2. The Rows, Columns, and Values should remain the same for both pivot tables, the only thing changing would be the Filter selection.

Notes:
- using Excel 2010, but moving to proplus soon.
- For the schools list I will always have a finite list(approx 100), so this is not something that will adjust.

Im assuming there is some fancy macro that I could write to get this done, but my vba skills can barely be called skills at this point. Please help!

Thank you

Excel 2013
[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH]_Row\^Col[/TH]
[TH]

A

[/TH]
[TH]

B

[/TH]
[TH]

C

[/TH]
[TH]

D

[/TH]
[TH]

E

[/TH]
[TH]

F

[/TH]
[TH]

G

[/TH]
[TH]

H

[/TH]
[TH]

I

[/TH]
[TH]

J

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

3

[/TD]
[TD]School[/TD]
[TD]Grade[/TD]
[TD]subject[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

4

[/TD]
[TD]School1[/TD]
[TD]1st[/TD]
[TD]Math[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

5

[/TD]
[TD]School2[/TD]
[TD]2nd[/TD]
[TD]reading[/TD]
[TD][/TD]
[TD]PVT1[/TD]
[TD][/TD]
[TD][/TD]
[TD]PVT2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

6

[/TD]
[TD]School3[/TD]
[TD]3rd[/TD]
[TD]writing[/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]School[/TD]
[TD="bgcolor: #DDEBF7"]School1[/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]School[/TD]
[TD="bgcolor: #DDEBF7"](Multiple Items)[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

7

[/TD]
[TD]School4[/TD]
[TD]4th[/TD]
[TD]science[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

8

[/TD]
[TD]School5[/TD]
[TD]5th[/TD]
[TD]physics[/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]Row Labels[/TD]
[TD="bgcolor: #DDEBF7"]Count of Grade[/TD]
[TD][/TD]
[TD="bgcolor: #DDEBF7"]Row Labels[/TD]
[TD="bgcolor: #DDEBF7"]Count of Grade[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #888888"]

9

[/TD]
[TD]School6[/TD]
[TD]6th[/TD]
[TD]history[/TD]
[TD][/TD]
[TD]Math[/TD]
[TD]

2

[/TD]
[TD][/TD]
[TD]history[/TD]
[TD]

1