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?

Tags:

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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

    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

  • Paul Newcome
    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; ")

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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

    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!

  • FWU
    FWU ✭✭

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

  • FWU
    FWU ✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!