Viewing/Combining Rows from separate sheets in 1 report

kellent
kellent
edited 01/16/24 in Smartsheet Basics

Hi all,

I am trying to build a report looking at 2 or more sheets, all of which have a Primary column called "Project Name", where the text in each Project Name column will match (the only common identifier between the 3 sheets will be the same project name). I want to pull certain columns from each sheet, but I want the report to aggregate by the matching Project Name in each sheet, and display the info on 1 row. I have found that you can "group" by the Primary column, but the report still shows multiple rows. See the attached pic, I think it makes it easier to see what I am looking to do.

In the example, I have 3 sheets all with 4 columns where Project Name is primary column

Sheet 1: Project Name, A,B,C

Sheet 2: Project Name, D,E,F

Sheet 3: Project Name, G,H,I


Can this be done? If not in Smartsheet, can the report be exported to Excel and this done there?


Thank you!

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭

    Hi @kellent


    Simple answer is that it cannot be done in Smartsheet when you use multiple sheets. If it is only a few columns you need from each sheet for the report, you can try adding the columns needed into one sheet and use cross sheet referencing formulas to pull the values needed into a single sheet. Then your report will be able to show the columns needed with just one sheet as the source. I would assume that you can try to export the data into excel and merge the data using some formulas, but I am in no means an excel expert.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training