Use filters from a different sheet

[Guest]

So, I have a workbook that consists of two sheet. On the first sheet I have a
bunch of raw data. I then applied an autofilter to all the columns. Next I
inserted several rows/columns of subtotals at the top.
On the second sheet Ihave differet charts that are fed from the subtotals on
the first sheet. This is great because the charts will change when I apply
filter criteria on the first sheet.

What I want is to be able to use the filters (from the fisrt sheet) on the
second sheet. I have tried creating another sheet that contains lists of the
unique values for each of the pertinent filter columns, assigning them names,
using drop-down lists and some code to apply the selected values as filters
against the first sheet. The problem here is that as I apply criteria to one
drop-down list, it does not subset the values in the rest of the drop-down
lists.... thus I can select mutually exclusive values from different
drop-downs. Also, there is no real hierarchy for filtering the different
drop-downs. (the drop-downs are things like: Regions, Areas, Cost Centers).
Thus, there is no real way for me to constantly rebuild the unique value
lists for each of the drop-downs.

Does anyone have a solution or idea on how to accomplish this? Any help is
greatly appreciated!
regards,
patrick

 

[Guest]

You could have a third sheet with a bunch of hairy array formulas that build
the unique lists (probably very slowly) and feed the data validation
dropdowns I assume you are using in the second sheet. Or you could build
these lists using code. In code You can build a unique list using a
collection or dictionary object.

the technique shown here uses a collection:

http://www.j-walk.com/ss/excel/tips/tip47.htm