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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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
    JLen ✭✭✭✭
    Answer ✓

    @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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!