Automatically update a summary page from different pivot tables

[johnbrownbaby]

Hello,

I have a few sheets in an Excel workbook. Each sheet contains a pivot table. I then have a summary sheet that contains columns from each of these pivot tables. An example of the original data looks like this:

Time	Desc	Temp	Prg_Freq	Meas_Freq	Test 1	Test 2	Test 3
1/0/1900 0:00​	Band1	25​	100​	101​	10​	9.5​	1​
1/0/1900 0:00​	Band1	25​	120​	122​	20​	19.5​	2​
1/0/1900 0:00​	Band1	25​	130​	129​	30​	29.5​	3​
1/0/1900 0:00​	Band1	-30​	100​	99​	11​	13​	8​
1/0/1900 0:00​	Band1	-30​	120​	120​	21​	23​	9​
1/0/1900 0:00​	Band1	-30​	130​	130​	31​	33​	10​
1/0/1900 0:00​	Band1	50​	100​	100​	9​	8​	2​
1/0/1900 0:00​	Band1	50​	120​	121​	19​	18​	4​
1/0/1900 0:00​	Band1	50​	130​	130​	29​	28​	8​

An example of a pivot table derived from this data is:

Sum of Test 1	Column Labels
Row Labels	-30​	25​	50​	Grand Total
100	11​	10​	9​	30​
120	21​	20​	19​	60​
130	31​	30​	29​	90​
Grand Total	63​	60​	57​	180​

Where I can usually change which Test to display, either Test 1, Test 2 or Test 3.

I then have a summary sheet, showing data from each tab based on the Test being selected. The cells in the summary sheet is a simple =TabCell. I want to show the same Test # on the summary sheet from each sheet. However, in order to do so, I have to go through each sheet, and update the same Test to display in each of the sheets, in order to correctly update the summary sheet.

Is it possible to update the summary sheet to show if I select Test 1 (for example), that all the pivot tables on each sheet, gets updated with the Test 1 filter, which in turn updates the summary page with just the Test 1 data?

If it is possible, how to go about doing this? Can it be done via a formula in the summary sheet or do I need a VBA script?

Any help will be sincerely appreciated! Thanks in advance.