How can I pull all projects associated with one team member into separate rows?

I need a report that shows all projects each employee is associated with under the Team Member column from a multi-select list. Because of the multi-select, I can't run a report and group by Team Member.

My work-around was to create a separate sheet that would just pull every project name associated with the employee on a separate row and I would run the report from there. I found =Join(Collect but it groups the information by commas. Smartsheet doesn't have a text to columns option and with the comma list being 10+ projects long, I couldn't figure out any of the LEFT or FIND formulas to separate the values to separate rows.

@Genevieve P. @Paul Newcome

I'm tagging you both as everything I've come across has your names on it with a solution.

Thanks for your help!



  • hollyconradsmith
    hollyconradsmith ✭✭✭✭

    Hi @mtk5200 ,

    Would it work to create a report that filters to each individual user, and then place all of those reports on a dashboard? I don't know how many assignees you might have to work with, but this is the "easiest" thing that came to mind.

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 12/10/21

    Hi @mtk5200

    I hope you're well and safe!

    To add to Holly's excellent advice/answer.

    • Another option could be to add so-called helper columns that break out the names from the multi-select columns and use that instead to group in a report.
    • A third option could be to use the JOIN COLLECT and break the data into individual rows.

    Would any of those options work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @mtk5200

    I agree with the suggestions above! I also answered your comment on another post (here) with a potential solution if you know the max number of projects per-person.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!