+ Reply to Thread
Results 1 to 4 of 4

Two pivot tables with the filter of pivot table 2 to automatically update to filter 1

  1. #1
    Registered User
    Join Date
    10-31-2016
    Location
    Melbourne
    MS-Off Ver
    13
    Posts
    33

    Two pivot tables with the filter of pivot table 2 to automatically update to filter 1

    Dear all

    I have two tabs with 2 pivot tables (1= Customer Analysis, 2= Product Analysis). In pivot 1, I want to choose the customer name in a filter; this customer name should automatically be pulled by pivot table 2 as well. How to do it? Please see attached "Tool" for my data.

    Moreover: what is the macro that automatically calculates all formulas fresh once I have filtered?

    Thanks and many kind regards
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Two pivot tables with the filter of pivot table 2 to automatically update to filter 1

    You can do this without VBA. I am assuming Version 13 means 2013. This means you have two tools at your disposal: Excel Tables and Slicers.

    I took your raw data and converted it into an Excel table and changed the source to this table. There are several advantages to excel tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

    Then I put a slicer on one of the pivot tables. To insert a slicer select a cell in the pivot table and use the Insert Ribbon -> Slicer. It's pretty much self-intuitive from there.

    If you select the slicer, there is a new ribbon Slicer Tools, this brings up an option on the right side of the ribbon called Report Connections. If the pivot tables are made from the same excel table, they can be controlled by the same slicer. You may find the slicer a more convenient way to select a filter. You don't even have to display an item in a pivot table to use a slicer to filter it.

    So, if you select items in the slicer, they will change both filters. Conversely, if you change the filter in one of the pivot tables, it will change the slicer which will change the other pivot table. If you don't want to see the slicer, put it on a hidden sheet. It will still link the two pivot tables together.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    10-31-2016
    Location
    Melbourne
    MS-Off Ver
    13
    Posts
    33

    Re: Two pivot tables with the filter of pivot table 2 to automatically update to filter 1

    works like a treat, many thanks! i only have the problem now that the "report connections" does not recognize 1 pivot (I just made a third one that I also want to attach). What could be the reason for this?

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Two pivot tables with the filter of pivot table 2 to automatically update to filter 1

    First, confirm that the third pivot table has as its source Table_Data. Then refresh the pivot table and see if that doesn't get the slicer to recognize it is there.

    if that doesn't work, delete the slicer and create a new one. It should not have to come to this.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 12-18-2016, 08:37 PM
  2. [SOLVED] How to get pivot table filter options to update?
    By Slurry Pumper in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-12-2016, 08:45 AM
  3. Automatically update pivot report filter
    By carlwin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2015, 07:57 PM
  4. Update Filter several Pivot Table by day
    By l.niehues in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2014, 10:04 AM
  5. Update pivot tables based on source data filter
    By papasmurfuo9 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-26-2014, 08:03 AM
  6. Pivot table Filter update through VBA code
    By way2suresh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-01-2013, 08:48 AM
  7. Filter several pivot tables by one programmed date range filter in Excel 2003
    By olewka in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-08-2011, 11:49 AM

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