JOIN COLLECT with two criteria


I have a sheet full of data from another source and trying to pull some of its contents into my main sheet.

The data sheet has a row for each speaker associated with a session and their role. So the sheet may have several rows with the same session title if there are multiple speakers.

The destination sheet has one row for each session and I want to collect any/all the Role 1 speakers into one column (multi-select dropdown), Role 2 in the next, and so on.

What I'm trying to make happen in the destination sheet:

I have a formula that will collect ALL of the Role 1/2/3 names into a single cell,

JOIN(COLLECT({Source Sheet Speaker Name}, {Source Sheet Role#}, =1), CHAR(10))

I can not figure out how to add in the extra layer doing the JOIN/COLLECT for cells from the source sheet only where it has a Title match.

Best Answer

  • Hollie205
    Hollie205 ✭✭✭
    Answer ✓

    Had a typo in my formula

    =JOIN(COLLECT({Source Sheet Speaker Name},{Source Sheet Role#},=1,{Source Sheet Title},Title@row), CHAR(10))

    It was missing a bracket and it should be your destination sheet title@row. Make sure all of your references are created correctly. I did a couple of fake sheets and set them up as you have above so it should work.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!