Filter Items from a Pivot Field

  • Thread starter Thread starter Allen
  • Start date Start date
A

Allen

I have a pivot table that includes data segmented by state and county among
other things. They are in the header portion of the table, not a column or
row. I'd like to make it so that whenever someone selects a state, only
counties from that state are visible in the county drop down. Is this
possible? Thanks for any help.
 
No, the page fields in a pivot table aren't dependent on what's been
selected in another page field. All the items will show in the dropdowns.
 
Did you manage to sort this one?

If not, I've done the same kind of thing in the past using validation and a
pivot table.

I would have two visible cells, both with validation applied, say A1 and A2.

In A1 I would have validation reference a list of the states (this could be
dynamic - using the offset formula - and sourced from a pivot table that
updates from your main data source).
So now A1 will always show all the states.

Now you would have a pivot table elsewhere, which shows all the counties
filtered by state. On the sheet of A1 _change procedure, you can add some
code to update the page filter of this pivot table. The validation of A2 can
now dymanically reference (once again using offset) the columns (counties)
from this pivot table.

If you are still working through this, write back and we can work through
the necessary code.

B
 
Back
Top