2-dimensional filter


I have hit a wall on ideas so thought I would reach out to the Smartsheet Community to see if anyone had any ideas, workarounds, or suggestions of how to accomplish a "2-dimensional" filter since there doesn't appear to be the capability (at least I'm not finding a way) to combine the "AND" and "OR" criteria to a filter.

Here is my scenario I'm trying to achieve...the report viewer should only see the sheet entries for their location and only the entries for a specific type. The Report Viewer could be one of 4 roles which are separated into 4 different columns (manager, finance manager, division manager, division finance manager).

The filter above includes all of the conditions, but the first 4 conditions would need to be "OR" (the report viewer could be in one of the columns), and then the last conditions needs to be an "AND" condition.

One workaround I thought of was to create an additional column named "Authorized Viewers" and allow multiple contacts per cell so then I would only need the 2 columns, "Authorized Viewers" and "Funding Type" to create my filter with a "AND" statement. But I want to eliminate any manual entries for the "Authorized Viewers". I tried "adding" all the users from the 4 columns into the "Authorized Viewers" column, but it doesn't keep the contact properties of each user when I have tried (i.e. the user name is plain text), unless I'm doing something wrong?? I need to keep the 4 contact columns separate because of approval request workflows which are sent only to the manager or division manager depending on the type of request, and would like to eliminate manual entries to the "Authorized Viewers".

Here is the syntax I used to "add" the contacts from the 4 columns into the 1 column, "Authorized Viewers":

=[Cost Center Owner]@row + [Finance Manager]@row + [Division VP/Mgr]@row + [Div/Corp Finance Mgr]@row

Any help and/or ideas to achieve the filter I need would be greatly appreciated. Thanks!


Best Answer

  • JLC
    JLC ✭✭✭✭✭✭
    Answer ✓

    Hi Joi, you should be able to combine AND and OR - there is a button at the bottom of the report builder window that says "+ New Filter Group". You can drag the bottommost conditions you need to be AND down into there. Hope that helps!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!