Automatically update a summary page from different pivot tables

johnbrownbaby

New Member
Joined
Dec 9, 2015
Messages
38
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:
TimeDescTempPrg_FreqMeas_FreqTest 1Test 2Test 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 1Column 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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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?

Disable AdBlock

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
Back
Top