Select Multiple Values Using IF Formula In MultiSelect Dropdown

Options

Hi Smartsheet Community,

Recently I've been working on a column that allows me to assign required approvals based on certain roles (see below).

I want to create a formula that can output an role if it's associated contact has been filled in or not. So for the example below, I would like it to output Project Owner AND Man & Ops because both of their contacts have been added. However, the formula I have is only able to pick up on the first true condition, and ignores the other one.

For reference, this is the formula I've been using so far:
=JOIN(IF(NOT(ISBLANK([Project Owner Contact]@row)), "Project Owner", IF(NOT(ISBLANK([Man & Ops Contact]@row)), "Man & Ops")))

I was wondering if there was any way for me to output two or more values based on whether or not their associated role has a contact in it's designated contact column.

Please let me know if that would be possible. Thanks for your time!

Best Answer

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    Hey @ahsan_mazhar101,

    You can! Here's the basic structure of how you would do that in the multi-select dropdown:

    =IF([Project Owner Contact]@row <> "", "Project Owner") + CHAR(10) + IF([Man & Ops Contact]@row <> "", "Man & Ops Contact") + CHAR(10) + IF([Engineering Contact]@row <> ""…..

    Obviously you'd need finish out the rest of the formula with the rest of the contact cells, but that's the basic structure. You want to also make sure you have the CHAR(10) between the IF statements as that's how the system knows to separate the different options.

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    Hey @ahsan_mazhar101,

    You can! Here's the basic structure of how you would do that in the multi-select dropdown:

    =IF([Project Owner Contact]@row <> "", "Project Owner") + CHAR(10) + IF([Man & Ops Contact]@row <> "", "Man & Ops Contact") + CHAR(10) + IF([Engineering Contact]@row <> ""…..

    Obviously you'd need finish out the rest of the formula with the rest of the contact cells, but that's the basic structure. You want to also make sure you have the CHAR(10) between the IF statements as that's how the system knows to separate the different options.

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!