Worksheet Automation to reapply the active Filter when a specific cell is updated

I have a case where I want to automatically reapply an active filter whenever I make a change to a specific cell. In this case the cell is in the header (not a value within a column of the table).

Screen Shot 12-09-21 at 12.48 PM

This spreadsheet works by using a “Filter” column within the table that has a formula returning TRUE or FALSE based on the value chosen in the Customer field. The filter is set to display only the rows where the Filter column equals “true”.


This is working pretty well, but it would work even better if the active filter could be reapplied automatically.

Something like this could be set up with a Worksheet Automation if you were allowed to both (a) trigger based on a change in a specific cell and (b) reapply the current filter as the action.

The template that uses this can be accessed from here:

1 Like

Very nice use-case! We have been toying with the idea of introducing a ‘always on’ flag on Views. Ie, the View auto-re-applies on a relevant change.

BTW what you are trying to do is similar to a ‘slicer’ component.


Yes, this example is similar to using a slicer with a pivot table … except in this case the table being filtered is the data source itself rather than a separate pivot table or report.

I like the idea of an optional ‘always on’ flag for filter views and I’d use that for cases like this. I probably wouldn’t use a flag like that for a sort because having the row I was editing change locations all of a sudden could get annoying, but that’s why an optional flag would be good - it allows the user to switch the behavior based on how they are working at the time.

With an ‘always on’ flag, you could do things like use checkboxes for to do lists where the task is automatically hidden when you check the box. It would also be fun if the UI used an animation to hide the row. Also note that undoing the edit that caused the row to be filtered should trigger the filter to be reapplied.

Space in a toolbar is precious, but another option would be to include a “refresh” or “reapply” button on the main toolbar just to the left of the filter button which appears only if there is an active filter or sort. It would also be nice to have a keyboard shortcut for that command such as the Alt+F5 shortcut in Excel for “Refresh”. I never remember the Alt+F5 shortcut unless I’m using it a lot over a short period of time, so having these shortcuts included in the tool tips is very helpful.

1 Like