Merging a large amount of data from multiple sheets

Janice O.

Hello,

I am hoping I can get some assistance with this. We have a Work In Progress report that is currently housed in google sheets, but we are looking into moving it over to smartsheet. In Google Sheets, the report has 4 different tabs. All 4 tabs are in the same order (by project), but house different data on the project.

1 - Summary - this tab pulls all the data from the 3 other tabs so we can view it all in one place and is not editable from this sheet (only from the source tabs). This tab is 150+ columns x approximately 120 rows.

2 - Acct Mgr - this tab is what our account managers use to enter their input sheet data.

3 - Production - this tab is for the production team to track the progress of the project.

4 - Admin - this is the tab used in office for tracking of progress / other data on the project.

Since Smartsheet doesn't have tabs, I created individual sheets for each tab. The issue I am running into now, is for the summary sheet. It taps out at 20,000 cell link references so I am not able to reference all the data we need in there. I also tried doing it as a report, but it doesn't merge the rows together, instead it creates one row per sheet (which means I have 3 rows for each project to look at). Is there a better way to do this??

Genevieve P.

Hi @Janice O.

Do you have access to WorkApps or the Premium add-on Dynamic View? Both of these would allow you to filter the data to allow specific people to edit the Report without needing access to the underlying source sheet.

Reports cannot merge data on to the same row, and the cell-linking limit is set for sheet functionality. You can provide feedback to the Product team about your use case and scenario through this form, here.

Thanks,

Genevieve

Genevieve P.

Hi @Janice O.

What about organizing it the other way around?

One source sheet with all the data in one place, your Summary sheet.

Then to break it out into your three other sheets you could create 3 Reports instead. These Reports would look at the same source row but surface only the relevant columns for each team.

It would mean that you'd need to share everyone with Editor permissions in both the Sheet and the Report so they can edit directly within the Report.

Otherwise, if you want to keep it as 3 Sheets and 1 Report, you could use the Grouping feature to collect the triple rows together (see: Configure grouping to organize results in report builder)

Cheers,

Genevieve

Janice O.

Hi Genevieve,

I did think about this but I don't think this would work as we only want Account Managers to have access to edit the acct mgr data, the production team to the production data, and so on. The summary sheet is for viewing only as not everyone has access to edit all the data on there.

I did try the Grouping feature but this makes it so that a lot of the data is repetitive as some of the basic data (such as project name, number, type, etc.) is on all of the sheets. Do you know if there is a way to increase the limit of cell linking? or a way to create a report where if the primary on the rows are the same it merges them together on the report? Kind of like how it merges columns with the same name.

Genevieve P.

Hi @Janice O.

Do you have access to WorkApps or the Premium add-on Dynamic View? Both of these would allow you to filter the data to allow specific people to edit the Report without needing access to the underlying source sheet.

Reports cannot merge data on to the same row, and the cell-linking limit is set for sheet functionality. You can provide feedback to the Product team about your use case and scenario through this form, here.

Thanks,

Genevieve