Filter a table for use in formulas

[dd14]

Hello, I have an excel file with

1. Source worksheet - has a table (about 30 columns or so, 3000 or so rows) .
2. Summary worksheet that has a bunch of "tables" (not true excel tables, but summary tables that have COUNTIFS/AVERAGEIFS etc types of formulas), and pivot tables that summarize the data from the source sheet.
3. Chart sheet that builds charts based on the summary sheet (all the tables/pivot tables) - multiple charts.

This is functional currently.

The users want a view of these charts that lets them filter on one column from the source sheet that is "Y" or "N". So, if they select "Y", all the charts should represent the data from the source worksheet that have "Y" in that one column. If they select "N", the charts should update to represent the data from the source worksheet that have "N" in that column. And they'd like an "ALL" option that shows all rows.

Usually, this is a job for a slicer - however, since some of the charts are not pivot tables, the slicer won't work. I could code the formulas on the "summary" sheet to use the slicer selection, but that is tedious (about 60+ charts). Is there an easier option that I'm overlooking? Any way to make the formulas in the Summary worksheet to use a filtered set of rows from the source worksheet perhaps?

 

[dd14]

Would it make sense to use the FILTER function to create a separate sheet (subset of the Source worksheet), and then, point all formulas on summary worksheet to the new worksheet?