Can I use an IF formula to return a value where the reference column is a multi option drop down??

I am trying to create an IF or CONTAINS formula that references a multi option dropdown cell to returns an email address for every department selected in the cell.

i.e. 'Other Departments Impacted' CONTAINS "Sales" "Accounts" "Delivery" so the 'Associated Contacts' cell returns "John Citizen", "Fred Smith", "Tony Dory".

Is this possible?

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@FWU

Have you thought about using Automation to Assign People?

The tricky part is that you can't have any subsequent actions after Assign People in the same automation. But, depending on how many different departments you have, you could make a condition path for each possible combination of departments that might be listed in the Other Departments Impacted (ODI) column.

Condition path 1: If ODI has Sales, Assign John Citizen

Condition path 2: If ODI has Accounts, Assign Fred Smith

Condition path 3: If ODI has Delivery, Assign Tony Dory

Condition path 4: If ODI has Sales and Accounts, Assign John and Fred

Condition path 5: If ODI has Sales and Delivery, Assign John and Tony

Condition path 6: If ODI has Sales, Accounts, Delivery, Assign John, Fred, and Tony

Condition path 7: If ODI has Accounts and Delivery, Assign Fred and Tony

All comments

Paul Newcome

You can output the names/emails, but they will not be usable as contacts for alerts/notifications by "adding" the IF statements together.

=IF(CONTAINS("Sales", [Other Departments Impacted]@row), "John Citizen; ") + IF(CONTAINS("Accounts", [Other Departments Impacted]@row), "Fred Smith; ") + IF(CONTAINS("Delivery", [Other Departments Impacted]@row), "Tony Dory; ")

Jeff Reisman

@FWU

Have you thought about using Automation to Assign People?

Condition path 1: If ODI has Sales, Assign John Citizen

Condition path 2: If ODI has Accounts, Assign Fred Smith

Condition path 3: If ODI has Delivery, Assign Tony Dory

Condition path 4: If ODI has Sales and Accounts, Assign John and Fred

Condition path 5: If ODI has Sales and Delivery, Assign John and Tony

Condition path 6: If ODI has Sales, Accounts, Delivery, Assign John, Fred, and Tony

Condition path 7: If ODI has Accounts and Delivery, Assign Fred and Tony

FWU

https://community.smartsheet.com/discussion/comment/335840#Comment_335840

Thanks Paul, I need alerts / notifications to make this sheet work. Jeff has given me a solution.

FWU

https://community.smartsheet.com/discussion/comment/335852#Comment_335852

Thanks Jeff, this has fixed it! As long as I unselect 'Replace existing values in multi-select column' when creating the automation, it will list multiple contacts in the cell. 😁

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.