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.
Bookmarks