+ Reply to Thread
Results 1 to 3 of 3

How to filter Top 10 values on 1 pivot table & reflect filtered items on all pivot tables

  1. #1
    Registered User
    Join Date
    12-14-2016
    Location
    Atlanta, Georgia
    MS-Off Ver
    Office 2013
    Posts
    2

    How to filter Top 10 values on 1 pivot table & reflect filtered items on all pivot tables

    Please see attached file as you read along.

    SCENARIO:

    -3 pivot tables based off of the same data source.
    - Each pivot table has 2 columns: (A)School name & (B)Mean SAT section score


    Need to program buttons, which should do 2 things:

    1) Filter top 10 items by value in one pivot table &
    2) show only those filtered items on all other pivot tables in the sheet.

    So for example, all pivot tables should then reflect top 10 schools by Mean Math section scores (even the pivots with Reading section & Writing section scores)

    In total, looking to have 6 buttons: three buttons for top 10 & three for bottom 10

    Beginner at VBA. How do I go about this?

    Note: This scenario is a replica of my problem with sample data as I can't share original.

    Thanks!
    Attached Files Attached Files
    Last edited by nsansari; 12-14-2016 at 05:28 PM.

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

    Re: How to filter Top 10 values on 1 pivot table & reflect filtered items on all pivot tab

    Frankly you don't need VBA with the way you have the data set up.

    I converted the raw data to an Excel Table so when the number of rows changes you don't have to change the pivot tables or the formulas.

    I got the top 10 schools in each area, and then used MATCH() and INDEX() to look up the scores from the other two areas.

    I noticed that in your original data the top 10 schools were the same in each area. I played with the data to test out when this is otherwise.
    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
    12-14-2016
    Location
    Atlanta, Georgia
    MS-Off Ver
    Office 2013
    Posts
    2

    Post Re: How to filter Top 10 values on 1 pivot table & reflect filtered items on all pivot tab

    Sorry, I should have mentioned this is part of an Excel dashboard showing supplier metrics.

    I have slicers and charts based off of the pivot tables.

    Due to the large number of suppliers, need buttons to show just the top 10/bottom 10 suppliers by each metric.

    Appreciate your help.

+ 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: 0
    Last Post: 07-14-2016, 03:51 PM
  2. check filter items in pivot table
    By DougMcC in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-21-2015, 04:57 AM
  3. Get list of filtered values from pivot filter
    By mateoc15 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-19-2015, 10:21 AM
  4. Filter pivot items using pivot items from another table
    By DKolev in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-16-2014, 08:49 PM
  5. Pivot tables on different worksheets with variable filter items
    By lrreed in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-29-2013, 02:22 PM
  6. Replies: 2
    Last Post: 11-02-2012, 10:38 AM
  7. Change Pivot table Filter Based on Cell Value *Multiple Filter items* Possible?
    By Flydd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2012, 06:57 AM

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