+ Reply to Thread
Results 1 to 4 of 4

Automatically update a summary page from different pivot tables

  1. #1
    Registered User
    Join Date
    05-20-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    4

    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!
    Attached Files Attached Files
    Last edited by jimjones1958; 05-21-2020 at 12:04 AM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,586

    Re: Automatically update a summary page from different pivot tables

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    05-20-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    4

    Re: Automatically update a summary page from different pivot tables

    Thanks for your solution. I did not think about combining the data like you did. Sincerest Thanks!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,586

    Re: Automatically update a summary page from different pivot tables

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1