I have a workbook that has an Input tab, a dashboard tab, and numerous pivot table reports. The data on the input tab comes from an ACCESS database using the Connections option on the Excel Data tab. The dashboard tab contains references to all the cells on the Input tab in addition to more columns that contain calculations.
The end user of this report wants the Dashboard filtered four different ways but doesn't want to have to do it himself. I figured I could create four new tabs copying and filtering the data from the original Dashboard tab.
The problem is when I refresh the data from ACCESS the filtered columns in the four new tabs now contain #ref, i.e. they loose the reference to the original Dashboard. I can't do a vlookup because I'm filtering on more than one column.
Then I thought ok I'll change the sql in the ACCESS database adding a where clause and filtering the data from there. Apparently you can't use a where clause in sql when using the Connections option in Excel.

Am I missing something or am I just trying to do something more than Excel and/or ACCESS doesn't do.
Any help would be appreciated.

Thanks,