Results 1 to 4 of 4
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Dynamic Filter

    Hi All. I am hoping that the masters on this forum can provide an answer to this question.

    I have a table that has a similar look to the following:

    Code:
    Country   State   City
    England    Kent   Ashford
    England    Kent   Fordwich
    England    Kent   Brogdale
    Australia  NSW    Sydney
    Australia  NSW    Lismore
    Australia  NSW    Grafton
    USA        NY     New York
    USA        NY     White Plains
    USA        NY     Rye
    etc
    There is some other data that I want to add up (say, area, population, vehicle count, etc).

    If I run the above data through a pivot table and I have Country, State and City as filters at the top, then if I select USA under the first filter ... when I move to the second filter, I have a list that includes Kent, NSW and NY even though 2 of those combinations are invalid. Is there a way that I can have adaptive (or dynamic) filters such that only valid combinations are shown in the pivot table filters?

    I know that I can do this with excel drop down and dynamic range names / validation rules but then I have to replicate the pivot table functionality.

    BTW - I know that Kent is a county and not a state ... I am just being lazy.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    Re: I know that I can do this with excel drop down and dynamic range names / validation rules

    Although you can set up dependant-dropdowns based on what has been selected from a 'previous' dropdown, this is not as simple as it sounds. For example, using your data, it is straightforward to create dependant dropdowns that allow you to select USA from dropdown1, NY from dropdown2 and Rye from dropdown3. I would call this a 'valid' selection.
    However, having made these dropdown choices, in my experience there is usually nothing to prevent a User from then returning to dropdown1 and selecting England, leaving the three selections showng as England, NY and Rye respectively, which I consider an 'invalid' selection.

    In order to ensure 'valid' selections only, I believe you need to use VBA.
    In which case, with VBA, you can achieve what you require.
    I would create my pivot table with the required page fields, but have a Userform overlaid on top of the pivot's page fields (to 'hide' them). I would then use dropdowns on the Userform to 'control' the underlying pivot page fields.

    zeddy

  3. #3
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Although you can set up dependant-dropdowns based on what has been selected from a 'previous' dropdown, this is not as simple as it sounds. For example, using your data, it is straightforward to create dependant dropdowns that allow you to select USA from dropdown1, NY from dropdown2 and Rye from dropdown3. I would call this a 'valid' selection.

    However, having made these dropdown choices, in my experience there is usually nothing to prevent a User from then returning to dropdown1 and selecting England, leaving the three selections showng as England, NY and Rye respectively, which I consider an 'invalid' selection.
    True. When I set this up for a different spreadsheet, I had it such that if you changed a 'higher' drop down, it reset (ie cleared) any 'lower' drop down using VBA.
    In which case, with VBA, you can achieve what you require. I would create my pivot table with the required page fields, but have a Userform overlaid on top of the pivot's page fields (to 'hide' them). I would then use dropdowns on the Userform to 'control' the underlying pivot page fields.
    Interesting. I'll give this a whirl and see what I can knock up. I am assuming that the userform you are talking about is not a floating one but one that is 'attached' to tab in question and that the underlying pivot table filters are modified by the VBA as the user changes the userform drop downs.

    Can user form drop downs be multi-select so that I can capture that functionality of the pivot table?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  4. #4
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Ruff_Hi View Post
    Can user form drop downs be multi-select so that I can capture that functionality of the pivot table?
    To answer my own question ... no. Combo Boxes (the excel name for Dropdowns) from 'form control' cannot be multi-select. List boxes can be but you need vba to get the selected items.

    An idea is starting to form (ugly combination of combo boxes and expand into list boxes when clicked) that can replicate what I am after.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

Posting Permissions

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