Relationship Report - collect multiple sheets' data to one row based on an ID

Come up with a relationship type set up across different sheets in Smartsheet like how MS Access does with their tables. This will allow users to easily create reports using different sheets without having to have the sheets all mirror one another just for the information to flow properly.

You can set up relationships in MS Access across different tables. This way you can create a query that has different information from different tables all compiled into 1 Table, and from there create a report if you desire.

(Beginner Access Table)

(Intermediate Access Table)

(End Result)

In Smartsheet, you can't do that. If you try to create a report from 2 different sheets, this is the result...

Sign in to vote!

Find more posts tagged with

Idea Submitted

Comments

DonnaG

In a report, would be great to be able to use two sheets to feed a report and reflect the information from both sheets on 1 single row, avoiding duplicates if the same project is showing on the two sheets. If the project is on both sheets, I'd like to merge them to the same rows. Right now, they are on separate rows, even if the column names and similar matches.

Courtney S.

This would be a really neat new kind of report to have! We definitely have situations where the same participant ID is in multiple sheets tracking different kinds of work. It could be useful to have a report that showed one row per participant, with participant data from more than one sheet.

Gabriel Barrera

Hi!

I think it would be a great idea if we could merge rows from different source sheets based on a unique identifier in a report. I know Control Center does this in it's own way using blueprint summaries but this is different.

For example, I use the copy row automation from one sheet, to split into two different sheets. So essentially, this row or project is now duplicated. One sheet is used for financials, and the other for operations. Both sheets share standard project info, but differ in other columns respectively. If I want to combine the two views into one, I would create a report using the two source sheets. But, they appear as two different rows, which is totally expected!

I think if there should be way in the report settings to "Merge rows when _____" and input your own criteria. Like "Merge rows when (column name) Project Name are the same". This way I get to use both of the sheet's different columns, with the same row.

Janae G.

This would be so useful! It would be similar to an INDEX MATCH or Data Mesh by a unique ID, except within a report. This would cut back on unnecessary extra columns in sheets that are pulling data from other sheets with an INDEX MATCH just so that data can be on a report from a particular sheet.

Quick Links

Trending ideas

Resource Management Plan Settings in Control Center - add Client and Project Code fields
In the Resource Management Plan Settings in Control Center, there is no way to link the Client and Project Code fields even though they are not Custom fields and part of the first 3 fields you enter when creating a new project. I can't select them in the Custom Resource Management Information Fields because they are not…
WorkApp Analytics
I’d like to see user analytics for WorkApps. I’ve checked the user report available in the Collaborator Pack, but it only shows how many times someone has clicked into the WorkApp each month. I’d like to know: Which resources (dashboards, sheets, forms, etc.) are getting the most traffic? Where are people spending their…
Sheet Icon Color Consistency
The 'Sheet' icon appears purple in the left navigation but blue within the sheet, creating inconsistency. Aligning icon colors across the platform would improve clarity and simplify user training. Please standardize the icon color to enhance user experience and reduce confusion during onboarding.