Pivot Table Dynamic Filter

Topic

New Reply

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:

[B]Country   State   City[/B]
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.

Reply | Quote

Replies

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 [/B]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

Reply | Quote

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 [/B]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?

Reply | Quote

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.

Reply | Quote