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?
Best Answer
-
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
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
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; ")
-
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
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks Paul, I need alerts / notifications to make this sheet work. Jeff has given me a solution.
-
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. 😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 412 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 140 Just for fun
- 57 Community Job Board
- 461 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
