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

alcapps · 10/08/20

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.

Ramzi K · 10/09/20

@alcapps

Here's one way to do it:

System Affected is a multiselect Dropdown with options A, B, C, D

Helper Row 1 (shown in green shading above) has same options as Dropdown

Approvals Complete is a Checkbox column with this formula starting in row 2 and drag-copy down:

=IF(IF(CONTAINS($[System A Approvals]$1, [Systems Affected]@row), IF([System A Approvals]@row = "Yes", 1, 0), 1) + IF(CONTAINS($[System B Approvals]$1, [Systems Affected]@row), IF([System B Approvals]@row = "Yes", 1, 0), 1) + IF(CONTAINS($[System C Approvals]$1, [Systems Affected]@row), IF([System C Approvals]@row = "Yes", 1, 0), 1) + IF(CONTAINS($[System D Approvals]$1, [Systems Affected]@row), IF([System D Approvals]@row = "Yes", 1, 0), 1) = 4, true, false)

I hope this helps you.

Cheers,

Ramzi

Ramzi K · 10/09/20

@alcapps

Here's one way to do it:

System Affected is a multiselect Dropdown with options A, B, C, D

Helper Row 1 (shown in green shading above) has same options as Dropdown

Approvals Complete is a Checkbox column with this formula starting in row 2 and drag-copy down:

=IF(IF(CONTAINS($[System A Approvals]$1, [Systems Affected]@row), IF([System A Approvals]@row = "Yes", 1, 0), 1) + IF(CONTAINS($[System B Approvals]$1, [Systems Affected]@row), IF([System B Approvals]@row = "Yes", 1, 0), 1) + IF(CONTAINS($[System C Approvals]$1, [Systems Affected]@row), IF([System C Approvals]@row = "Yes", 1, 0), 1) + IF(CONTAINS($[System D Approvals]$1, [Systems Affected]@row), IF([System D Approvals]@row = "Yes", 1, 0), 1) = 4, true, false)

I hope this helps you.

Cheers,

Ramzi

alcapps · 10/12/20

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,

Audrey

Ramzi K · 10/12/20

@alcapps

Glad to help.

Ramzi

RSR_TSB · 05/11/22

https://community.smartsheet.com/discussion/comment/265704#Comment_265704

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

alcapps · 05/23/22

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

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

Best Answer

Answers

Help Article Resources

Categories