Is there a way to write a formula based on a multi-select field

We require approvals based on the systems affected by non-exempt change requests once we send for review. For example, if system A is affected, then managers 1, 2 & 3 must approve and if system B is affected, then managers 2, 3 & 4 must approve. I need a formula to show when all required approvals are complete. The systems affected field is a multi-select field so if systems A and B are chosen, then the approvals complete field would be blank until managers 1, 2, 3 & 4 have approved (then it would show "Yes").

Because we have several systems (9) and several managers (21), I have created supporting columns with formulas specific to each system affected. I'll call those columns "System A Approvals Complete" - =IF(AND([Send for Review]@row <> "", [Request Type]@row <> "Exempt", CONTAINS("A", [Systems Affected]@row), [Manager 1 Approval]@row = "Approved", [Manager 2 Approval]@row = "Approved", [Manager 3 Approval]@row = "Approved"), "Yes", "") and "System B Approvals Complete" - =IF(AND([Send for Review]@row <> "", [Request Type]@row <> "Exempt", CONTAINS("B", [Systems Affected]@row), [Manager 2 Approval]@row = "Approved", [Manager 3 Approval]@row = "Approved", [Manager 4 Approval]@row = "Approved"), "Yes", "").

The problem I'm having is my formula isn't evaluating all scenarios. This is what I tried...

=IF(AND(AND(CONTAINS("A", [Systems Affected]@row), [System A Approvals Complete]@row = "Yes"), IF(AND(CONTAINS("B", [Systems Affected]@row), [System B Approvals Complete]@row = "Yes"))), "Yes", "")

Hope this makes sense.

Best Answer


  • alcapps
    alcapps ✭✭✭✭

    Thank you, this worked perfectly! I chose not to use the helper row since new requests load to the top of the sheet, so I used the system names instead.

    Thanks again,


  • Ramzi K
    Ramzi K ✭✭✭✭✭


    Glad to help.


    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (

    Feel free to email me:

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

    RSR_TSB ✭✭

    Audrey, which formula did you end up using instead of the helper rows?

  • alcapps
    alcapps ✭✭✭✭

    I used the formula Ramzi posted but used column names in my formula instead of the helper row labels.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!