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.

Find more posts tagged with

Accepted answers

Ramzi K

@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

All comments

Ramzi K

@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:

I hope this helps you.

Cheers,

Ramzi

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,

Audrey

Ramzi K

@alcapps

Glad to help.

Ramzi

RSR_TSB

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

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

alcapps

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.