join, collect with multiple entries

This formula in the "Also Notified" column works perfectly for one "Deputy" with one or more "Deputy Back-Up Contacts"

=JOIN(COLLECT({Deputy Back-Up Contacts Range 4}, {Deputy Back-Up Contacts Range 1}, Deputy@row), ", ")

However, when more than one Deputy is selected, the formula should return all the Back-Up Contacts for all Deputies selected. Instead it comes back blank. (for example, Aaron has four Deputy Back-Up Contacts and Gabby has one, so five emails should be listed.


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Gabby Nepomuceno

    try this

    =JOIN(COLLECT({Deputy Back-Up Contacts Range 4}, {Deputy Back-Up Contacts Range 1}, HAS(@cell, Deputy@row)), ", ")

    Does that work for you?


  • Unfortunately, no. It still returns a blank cell. Is there a way to string together two JOIN functions? One that joins all the contacts listed in the Deputy column and the associated Back-Up Contacts?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey Gabby

    Somewhat surprisingly, there isn't a straight-forward way to do this. I had to do something very similar on one of my sheets this past week and, because I very rarely have more than three names in your equivalent of "Deputy", I parsed the names out individually into helper columns and then joined multiple collects together. I couldn't figure out another way to do it. Is the number of deputies a small number?

    If you're interested in parsing into multiple helper columns, I'll be happy to help. Or, feel free to mark the post as unresolved and the community will give it a try. There was a question asked today regarding multi-selects and collects. You might want to watch any answers to that question.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!