Combining Two Sheets into a Report

Combining Two Sheets into a Report

latsyrcg3latsyrcg3
edited 12/09/19 in Using Smartsheet

I have two task lists with slightly different columns, but there are some overlap - like status, completion date, & targeted completion date. I would like to run a report or some how combine all the tasks into one report/view/sheet - while continuing to maintain the two task list as separate sheets. I tried to create a report with two sources, but it didn't work (when I create a report for each sheet separately, it works). I assume that it isn't working because the columns aren't exactly the same - but then what is the point of a report? 

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi,

    You can add all the needed columns from both sheets.

    Would that work?

    Have a fantastic week!

    Best,

    Andrée Starå - Workflow Consultant @ Get Done Consulting

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • When creating the Report, in the Where section, you can select multiple sheets.  Or if in a Workspace, you can select the entire Workspace and it will pull from all Sheets within that Workspace.  As an example, I have a standard sheet per product (~20 of them), and from that, I create an Exec Summary Report to pull common columns from all 20 sheets into a single Report.

    Hope that helps.

  • I have the same question.  Example:

    Sheet 1 has personnel info of who is assigned to a project

    Sheet 2 has software info for each project

    Both Sheet 1 and  Sheet 2 have an identical column - ArchivePrjNum

    When I try to create a report that has info from both sheets, I get two rows for each project.  Please see the image below for a very simple version of the report.

    Notice two rows for each ArchivePrjNum.  One row contains the project number and a contact while the other row contains the same project number and database size.

     

    Any help would be appreciated.   Thanks

    2019-11-26_082146.jpg

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Paul,

    There are a few different ways to structure a solution.

    To have the values side by side, we would either use cell-linking or cross-sheet formulas to collect everything together in one sheet.

    Would that work?

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

     

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Thanks for the reply.  I've attached three images.  They show Sheet 1 (Personnel), Sheet 2 (Software specifics) and the report with nothing more than the common item on both sheets (ArchPrjNum), one from Sheet 1 (Department Owner) and one from Sheet 2 (Database Size in TB)

     

     

    Report with double entries for each PrjNum.jpg

    Sheet 1.jpg

    Sheet 2.jpg

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Happy to help!

    How many rows can there be in each sheet?

    Are the ArchivePrjNum in the exact same order in both sheets?

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    • 75 rows on each sheet
    • They will not always be in the same order.  They may be sorted individually. 

     

    Paul

     

     

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Ok.

    Then we would use a VLOOKUP formula looking at each sheet and collect them in a third sheet and then use that one for the report if needed.

    Would that work? Are you familiar with the function, VLOOKUP?

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Hi Andree,

    Do you have an example of the VLOOKUP formula in action :)

    I'm having the same issue that you are talking about here and I cannot build the formula in the report builder to merge the data.

    Please help!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Marilu Yanes You would need to build the formula in a sheet and then reference that sheet in your report builder. Formulas can't be entered through reports.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @Marilu Yanes

    I'd be happy to share an example. 

    Please send me an email at [email protected], and I'll share it with you.

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

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

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

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.