I’m making a spreadsheet where I can use autofilter to filter certain values. In the example below, I want to be able to filter on the Status column (B), with values “In Progress” or “Finished”.
The gray rows are “subrows” of the main rows. I want them to disappear as well when the main row is filtered.
I found a way to do that, by using this formula in the A column (cell A3): =IF($B3<>"", AGGREGATE(3, 7, $B3), $A2)
With aggregate I check if the the cell is hidden or not.
This works. I can filter by Status (column B), and then filter the A column to only show 1 and not 0.
But when I set the filter in column A, I can’t undo the filter in column B anymore. The values that are not shown are grayed out. This makes sense, the filter in column A is preventing those rows from being visible anyway. But the thing is - if the filter in column B would change, it would also update the values in column A, making the rows visible again.
Is there a way to get around this?
