join, collect with multiple entries

Gabby Nepomuceno

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

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?

Kelly

Gabby Nepomuceno

https://community.smartsheet.com/discussion/comment/327004#Comment_327004

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

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.

Kelly