Is there a way to write a formula based on a multiselect field
We require approvals based on the systems affected by nonexempt 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 multiselect 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

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 dragcopy 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 Khuri  Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Answers

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 dragcopy 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 Khuri  Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

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 Khuri  Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

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

I used the formula Ramzi posted but used column names in my formula instead of the helper row labels.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 200 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!