Dynamic View - Filter on current user for multiple columns (or) plus a row status (and).

Options

Situation:

I'm trying to develop a dynamic view to a large request processing log and filter this based on both current user (there is a need-to-know element here) and a row status condition (to filter out completed work).

Problem:

The log has assignments across 7 different task categories, and I need to filter for the current user in any (not all) of these columns. Dynamic view was only able to look at this on a single column basis, so I opted to create a sheet filter with an (or) statement to pull 'current user' from any of these fields. No problem here.

I then want to filter out completed work based on the status column. Neither Dynamic View nor the Sheet Filter function let me add an additional filter that would achieve this. Of course, I can add a filter for this column in the sheet filter, but all the layers of the sheet filter are collectively treated as 'or' (will break the check for 'current user') or 'and' (requires the current user to be named in all 7 categories, which won't happen), with no option to more explicitly configure combinations of filters.

Ideas:

If this weren't leveraging the 'current user' function, I could solve for this with a formula / helper column to return a 1 or 0 and build the filter from that, but as far as I'm aware, there's no formula component to look dynamically at 'current user'. I'm not sure if this would actually work anyway since I don't know if Dynamic View is actually opening the source sheet on behalf of the current user, or if it's just doing a lookup for a match to what it sees as the 'current user' against data in the source file.

This leads me to think the only remaining approach is to develop a separate report where I can combine (and) and (or) filter criteria, and build the dynamic view from that. This may be an option, but I'm hesitant as the intent here is to allow editing and modification of fields in the sheet, and while I know that's possible via reports, I don't know how well that will play together with dynamic view.

Thanks in advance for suggestions on how best to tackle this.

Answers