Evaluate multi-select field to determine multiple contacts
Hi all. I need help. I have a form where people can complete to be able to sign-up for newsletters around certain topics (screenshot 1 below are responses from form). From there, on another sheet, I want to identify the subscriber for each newsletter topic (screenshot #2 below). I think I have done this before using a Join(Collect or Index(Match with "HAS" - but despite searching the community and trying several formulas, I'm failing. Anyone have an idea?
On screenshot #2, Sally Smith and Jim Jones should be listed next to commercial and government affairs and Sally Smith should be the only one listed by Labor & Employment, Litigation, and Privacy.
Thank you in advance for your help (and ideas),
Jen
Best Answers
-
Hi @JLen
You are correct! JOIN(COLLECT with HAS should get this data for you. Try something like this:
=JOIN(COLLECT({Email Column}, {Type of Newsletter Column}, HAS(@cell, Newsletter@row)), CHAR(10))
I've used a line break as the value to Join by, so that you can use Wrap Text to see each email on its own line in the same cell. See: Gather all matching content into one cell
Let us know if this works for you!
Cheers,
Genevieve
-
@Genevieve P. - I have a follow-up to this. The formula is working but despite the fact I'm using a contact list in each of the fields, the emails are not showing as contacts and so my automation to them are not sending. Any thoughts on how to fix that?
Answers
-
Hi @JLen
You are correct! JOIN(COLLECT with HAS should get this data for you. Try something like this:
=JOIN(COLLECT({Email Column}, {Type of Newsletter Column}, HAS(@cell, Newsletter@row)), CHAR(10))
I've used a line break as the value to Join by, so that you can use Wrap Text to see each email on its own line in the same cell. See: Gather all matching content into one cell
Let us know if this works for you!
Cheers,
Genevieve
-
Thank you so much, @Genevieve P. I had the @cell after Newsletter@row, but everything else was right! Soooo close. Thank you for your help!
-
@Genevieve P. - I have a follow-up to this. The formula is working but despite the fact I'm using a contact list in each of the fields, the emails are not showing as contacts and so my automation to them are not sending. Any thoughts on how to fix that?
-
Hi @JLen
It's currently not possible to combine contacts into one cell using a formula and have them be seen as a Contact type of item in Smartsheet. Please feel free to add your vote and voice to this Product Idea:
Combine Contacts from multiple columns into a single cell
Cheers,
Genevieve
-
Thanks so much, Genevieve. Will definitely add my vote!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!