Create a list from check marks in a grid


I need to pull data from a grid organized like this (with yellow items being being column headings):

into a list that is structured like:

I am stumped on how to do it. I do not own the original data so I cannot restructure it into a different form.

Any tips?




  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Matthew Barrett

    You could use a cross-sheet JOIN(COLLECT formula to Join together in one cell all of the different Teams associated with each Resource, but the formula wouldn't be able to parse the details into multiple rows.

    Here's an example of what that might look like:

    Note that for each Resource you'll need to create a unique cross-sheet reference to that specific column. In my formula I'm presuming that your Resource columns are checkbox columns, where 1 = a checked box.

    Here's the formula structure:

    =JOIN(COLLECT({Team}, {Resource 1}, 1), ", ")

    Will this work for you?



  • Unfortunately I need the pulled data in multiple rows so that I can use that to pull from yet another set of data in order to create a report.

  • Devin Lee
    Devin Lee ✭✭✭✭✭

    Allow for blank answers and just filter them out.

    =IF([Resource 1]1 = 1, Team1, "")

    =IF([Resource 1]2 = 1, Team2, "")

    =IF([Resource 1]3 = 1, Team3, "")


    =IF([Resource 2]1 = 1, Team1, "")

    =IF([Resource 2]2 = 1, Team2, "")

    =IF([Resource 2]3= 1, Team3, "")



  • The source data is much more complicated than I presented. There are thousands of "teams" and a dozen or so "resources". So far for each resource I have a sheet that pulls a list of teams using Index and Collect.

    This gives me a list of teams I can reference for the metrics associated with each resource but does not allow me to build the metrics by team.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!