Combining Two Sheets into a Report

latsyrcg3

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? 

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

Bruce Case

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.

pmacdonald

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

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, andree@getdone.se)

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

 

pmacdonald

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)

 

 

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?

pmacdonald

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

 

Paul

 

 

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?

Marilu Yanes

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 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å

Hi @Marilu Yanes

I'd be happy to share an example. 

Please send me an email at andree@workbold.com, 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!