Filter Organization

I recently setup something similar so that a coworker could avoid having to edit a filter. I will explain it in the context that I personally set up and hopefully that will help you modify as needed. If it sounds like something that could work for you, but you are unsure of how to modify it, feel free to let me know.

.

Context:

Coworker's task sheet contains multiple tasks for multiple clients. Coworker needed to be able to filter the sheet to see one client, multiple clients, or all clients, but the coworker did not feel comfortable regularly editing the filter itself.

.

Solution:

Client Selection Sheet (this would be your list of criteria to filter the sheet by):

I created a Client Selection sheet. The basics of this is that this sheet lists down a column each of the clients that have at least one task related to them. I have this in the Primary Column. I then have another column that is a checkbox type [Include in Filter]. Rows 1 and 2 of this sheet are used separately from the main client listing.

Row 1: 

Checkbox column: Blank

Primary Column: =IF([Include in Filter]2 = 1, "All Clients", JOIN(COLLECT([Primary Column]3:[Primary Column]52, [Include in Filter]3:[Include in Filter]52, 1), " "))

Row 2:

Checkbox column: Blank

Primary Column: "All Clients"

Basically the way this works is if the box in row 2 is checked to signify "All Clients", then the cell in the first row of the Primary Column will read "All Clients". Otherwise it will join each of the clients that are checked off with a space as a delimiter.

.

Task Sheet (this is the main sheet that the filter(s) will be applied to):

I then went back to my main task sheet. I added in a checkbox column that I called Filter.

The first row of this sheet is a header row, so I could use row 1 of this new column for the next step.

I used cell linking to link back to [Primary Column]1 on my Client Selection Sheet where each of the selected clients were joined together.

Then in the remaining rows, I was able to basically say that if Filter$1 contains either "All Clients" or contains the [Client Acronym]@row, check the box.

=IF(OR(Filter$1 = "All Clients", CONTAINS([Client Acronym]@row, Filter$1)), 1)

Next I built a filter that basically showed only rows where the Filter box was checked.

.

So now we can check one or more boxes on the selection sheet, save it, then refresh the task sheet for the new filter criteria to kick in.

To avoid having to open two different windows, I created a dashboard.

On the left is a URL widget that has the published task sheet. On the right is another URL widget containing the client selection sheet.

Since they are side by side on the dashboard now, my coworker just selects which client(s) they want to see, save it on the selector, then use the hotkey to refresh the browser window, and the sheet is updated with the listing. 

I know that having to save then refresh every time a different client is selected or deselected, but this gives us the ability to list out and organize the many different options for filtering the sheet and keeps it all in the same place using the dashboard.

.

If you wanted to keep it all on the same sheet to avoid the refreshing issue, you may be able to list your filters out either at the top under a parent row that can be collapsed or in the Sheet Summary section (sheet summary isn't a bad idea, but it is not very flexible if there are frequent changes to your list), and link that to the checkboxes that drive the filter.