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
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 386 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 125 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!