How can I pull data that needs to be matched from two sheets into a report?

Colleen Jones

Hi all,

I have created a sheet for our active projects and a second one for what we call document details. Sometimes we have multiple documents for one project, and they'll be in separate rows.

The one field in common is the Project ID. It is the primary column in one sheet but not the other. I don't know if that is important.

I've been playing around with a report, and I managed to get data from both sheets into the report, but they are creating separate entries. I'm not sure how to get the details for Project SP000014 in each sheet to be in the same row in the report. In the active sheet, the Project ID is auto-generated. In the other sheet, it's entered manually.

Here is a section of the active projects sheet:

Here is a section of the doc details sheet:

Can anyone give me tips on how to make this work? I've tried looking at formulas to pull data in but can't figure out how to match based on Project ID. I've tried creating an interim sheet, but don't know how to pull in the columns so the data is live in that sheet.

I've only been doing this for a couple of weeks, and we have to have this working by end of next week when our old system contract expires.

Thanks for any help or advice you can provide. Colleen

Austin Smith

Two options that I know of:

1) Completely combine all of the data into the same sheet and split the data you had in your previous database into reports instead of primary sheets (think of reports as filtered views of the main sheet). The reports are editable, just like the sheet, the problem here is that everyone you need to edit any of the reports will have to have access to all of the primary sheet. If you're looking for live, editable reports, I believe this is the only way. Sacrifice concision and data security for speed.

2) The way I've had to do it so that my data stays in the right hands and pointed the right direction with the least amount of confusion from my team is a bit of a headache, but it does work well once you understand the parameters. It will give you a product that is not editable, but is easy to play with on a macro level and quickly distribute.

I build out a couple of helper columns in all major sheets to use as cross reference (for your example, Project ID should be specific enough) and then I collate data into a separate sheet with Vlookup formulas aided by filters.

The interim sheet will need to have a column with all possible pre-generated project IDs so that it can vlookup against that reference point. Easiest to build that column in excel and paste it in (Smartsheet's not great at doing bulk entries). You'll have dead rows that will be removed via filter, but they need to be there for new additions to the other sheets.

=VLOOKUP([Project ID]@row, reference other sheet range, reference row, false)

Example below uses Project Number as primary reference (this is the column I built in excel). Concatenate Helper is my typical reference because it's more specific, but not so specific a typo could tank it, it finds only primary estimates on projects, and ignores addendums (looking for high level information here). Every other column in the sheet (appx 20) is pulled from another sheet via VLOOKUP. Typically this sheet only shows projects with a specific bid status, job status and start date.

I use tons of IFERROR, CONCATENATE, SUMIFS, AVGIFS and COUNTIFS to supplement the VLOOKUPS.

Colleen Jones

Hi Austin, thank you so much for your response. I think after thinkinga bout this overnight, that option 1 makes more sense for my team. We won't really have outsiders looking at our data, so it's fine if everyone can see everyone else's items. I watched the reports training video, and it looks like I can set up the report so that the "current user" automatically filters when they open the report.

Your other method looks interesting but too complicated for me given the time constraints I have and my current skill level.

Thanks again for these two options.