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; ")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
