Automatically update a summary page from different pivot tables

Hello,

I have a few sheets in an Excel workbook. Each sheet contains a pivot table derived from a RawData sheet. I then have a summary sheet that contains columns from each of these pivot tables. An example of the raw data looks like this:
2020-05-20_22-24-46.png

Where I can usually change which Test to display, either Test 1, Test 2 or Test 3:
2020-05-20_22-24-46.png

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 # data on the "Summary" sheet from each sheet. However, in order to do so, I have to go through each sheet, and update to 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) from the "Summary" sheet, 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.

P.S. I also posted this question on the MrExcel.com Forum. I did not get any response after a few days, so I decided to try this forum out. Thanks!

Perhaps this will be helpful.
I combined the data tables (see Combined Data sheet) using the following advanced editor code for power query (a free add-in from Mircosoft for Excel versions 2010 and later)

Please Login or Register  to view this content.

I then produced an pivot table from the new table.
Note that while I was combining the data I also transformed the Tests and Values so that Test could be chosen using the filter (or a slicer).
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.