+ Reply to Thread
Results 1 to 2 of 2

Is it possible to limit pivot filters to only options that fit other filters?

  1. #1
    Registered User
    Join Date
    09-17-2014
    Location
    Altadena, CA
    MS-Off Ver
    Office Pro 2013
    Posts
    3

    Is it possible to limit pivot filters to only options that fit other filters?

    I have a dataset with five filters, two of which have >1000 values, but all 5 filters are interlocked. The base dataset for the pivot is around 237k rows. I want to have the pivot table filters only show options that match the other filter selections

    Simple Example:
    Category Procedure
    A asdf
    A qwer
    B asdf
    B zxcv
    B Q

    If the user selects category A, I want them to only see procedures asdf and qwer when they look at the Procedure filter.

    So far I haven't found anything that would allow me to do that in a pivot table directly (please correct me if I'm wrong!), so I was thinking about possible programmatic solutions with pulldowns or list boxes instead along with database functions to get the values I need. Has anyone done something like this, or have a link to an article about doing it?

    Hypothetically, I could have the full lists for each of the five filters (along with an "All" option) on a hidden page, and then 5 pivot tables next to them, each with the other 4 filters.

    As the user selects one (or more) option on each filter, I could use VBA to make the same selection(s) on the other 4 pivot tables, and adjust the sources/selected values for each pulldown/list box via code. I'm going to work on it some more, if I figure it out I'll post my code.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Is it possible to limit pivot filters to only options that fit other filters?

    Hi,

    Your description is ambiguous

    When you say "If the user selects category A, I want them to only see procedures asdf and qwer when they look at the Procedure filter. "

    Do you meant with Cat A SELECTED you want to see BOTH asdf & qwer in the Procedure column, or do you mean when Cat A is selected you want ONLY asdf in the Procedures column and when you select Procedures you only want to see qwer.

    This is clearly a very simple example and presumably does not show all your permutations of real data. It would be better if you uploaded the workbook and in it manually create a table of what you would expect to see given various selections in a PT. I don't mean create a working pivot table, I mean mock up a pseudo pivot table result layout.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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: 7
    Last Post: 07-15-2019, 03:06 AM
  2. Filters : Not Equals <> with Filters is not working !!!!
    By vishulive in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-04-2015, 01:40 AM
  3. Pivot Filters to change simulataneously with other pivot filters
    By ScottBeatty in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 07-02-2014, 10:48 AM
  4. Using filters, and then unselecting filters. Organizing data.
    By lesoies in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 08-09-2013, 07:55 AM
  5. Usage of IF,AND function along with filters for a Credit limit calculating excel.
    By sree_kaushik in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-07-2012, 12:21 PM
  6. Replies: 4
    Last Post: 07-24-2012, 01:21 PM
  7. Limit number of filters shown in columns
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2010, 01:09 PM

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