Select Multiple Values using IF formula in Multiselect dropdown

Options
ahsan_mazhar101
ahsan_mazhar101 ✭✭
edited 05/14/24 in Formulas and Functions

Hello,

I recently created a multiselect dropdown column to track required approvals based on roles (see below).



What I want to do is automatically fill in values for that column based on if an associated contact column is filled in or not. So for the example shown below, I'd like to see "Project Owner" and "Man & Ops" be shown because both of those contact columns have a contact in them.

The formula I worked on is:
=JOIN(IF(NOT(ISBLANK([Project Owner Contact]@row)), "Project Owner", IF(NOT(ISBLANK([Man & Ops Contact]@row)), "Man & Ops")))

Unfortunately, that only gives me the first part ("Project Owner") and not "Man & Ops" too.

I was wondering if there was some way to make this work to where I can have both? I would really appreciate any feedback you could provide me. Thanks for your time 😁

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @ahsan_mazhar101

    Nested IF statements stop as soon as they find a successful output, which is why you're only seeing the first result. Instead of nesting them, try adding each IF statement together, with CHAR(10) in the middle. This acts as the space between individual values.

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

    Does that make sense?

    Cheers,
    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @ahsan_mazhar101

    Nested IF statements stop as soon as they find a successful output, which is why you're only seeing the first result. Instead of nesting them, try adding each IF statement together, with CHAR(10) in the middle. This acts as the space between individual values.

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

    Does that make sense?

    Cheers,
    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!