Combining Two Sheets into a Report

latsyrcg3 · 05/13/19

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å · 05/13/19

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 · 05/13/19

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 · 11/26/19

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å · 12/03/19

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

pmacdonald · 12/03/19

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å · 12/03/19

Happy to help!

How many rows can there be in each sheet?

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

pmacdonald · 12/03/19

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

Paul

Andrée Starå · 12/03/19

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 · 07/30/20

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 · 07/30/20

@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å · 08/01/20

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!

Combining Two Sheets into a Report

Comments

Categories