Can I group by contact in a report if the contact is not always in the same column?


I am trying to run a report from a sheet that contains many projects and different columns for each role within those projects. I have some staff members who serve in different roles in different projects. (For example, Abby is "research lead" on project A, but "Research Advisor" on project B.) I want to be able to run a report that groups projects by different people. That way I have a list of all the projects Abby is working on and all my other team members. However, right now Reports is only letting me group by different columns.

Any ideas?



  • James Keuning
    James Keuning ✭✭✭✭

    Presumably this report will contain more rows than you have projects, right? Because if we have two projects, and you and I are the only people who work on those project, and we each have a different role, you want the report to show four records. Your two, and then my two.

    I'd say that the native functionality of a Smartsheet report cannot do this. The only way I can think of doing it is to create a helper sheet, where you have each person listed, and then under each person you have the projects that the person works on, and then you run the Report from that sheet. But that's going to be painful to maintain, because you will have to insert rows and copy down formulas whenever you have a new project, or a new person comes onto a project.

    You could also create one sheet per person, and that might be a little more self-sustaining, and then pick all of the sheets as sources for the report.