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

Find more posts tagged with

Accepted answers

Genevieve P.

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

JLen

@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?

All comments

Genevieve P.

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

JLen

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!

JLen

Genevieve P.

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

JLen

Thanks so much, Genevieve. Will definitely add my vote!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.