Update a cell on a master list from individual identical laid out sheets

Doug Richards

I have a template that I use to create data sheets for unique projects. All rows and columns are the same for each project. I also have a master sheet that lists all the projects which are populated with information from the same cell location from each project sheet.

I have been able to go from the master sheet to the correct project sheet and update information in the correct cell of the project sheet, but I need to go the other way. When information is input into a certain cell, it will be updated in the master sheet in the correct row and column.

The project sheets are saved with the same project name that appears on the master sheet and I also have the project name in a unique cell (top/right) in all project sheets.

Any Ideas?

Thank you for your help

Julio S.

Hi Doug,

It sounds like creating a Row Report for the Master list of projects could be a possible solution. Note that if all projects are part of the same Workspace or Folder you can easily add all of them at once to the Sheet Scope. This would allow users with up to Editor permissions at both the Report and the underlying sheet to make edits at the Report which will also reflect in the relevant sheet. In addition, since the Report will be fed with the relevant sheets, any updates made at the sheet level will automatically reflect in the Report.

I hope that this can be of help. However, if this doesn't fully tackle the issue or I have misinterpreted you, please include some captures of your current solution and I'll be happy to continue advising. Please make sure to remove/hide any sensitive information when sharing your captures.

Cheers!

Julio

Doug Richards

Julio, thank you, your comments got me on the right track.

Another question:

I am tracking the patent process on many projects, each project has its own sheet that comes from a template, so all columns and rows headers are the same, entered data is unique. The master list with is from a summary sheet from each product sheet, this allows stakeholders to view status of all products in one report (this is what you helped with), now I have metrics about each product that if possible I don't want to enter into each product sheet but have it on another sheet where I can drill down using the metrics to create a dashboard charts showing the distribution of the products. I was hoping that I could find a way that when I added a new product sheet it would add it to the list and I would fill in the metrics for that product, but I'm thinking now that I need to API our product table (outside of Smartsheet) or create a Smartsheet table with all of the metrics and do some sort of INDEX lookup to create a summary report to create the numbers that Smartsheet can create the charts. If I haven't confused you with my lack of descriptive verbiage, any thoughts? Thanks again.

Doug

Julio S.

Hi @Doug Richards,

Without visualizing your sheets and template structure is a bit difficult to tell but it does sound like creating a set of Sheet Summaries in each Project/Product sheet and aggregating the data into a Sheet Summary Report could be a potential solution since you may then use the aggregated data from the Report in a similar fashion as with the previous example of master Report.

Note, however, that if you also plot your data out of the metrics into Charts, the Chart Widget isn't available for Sheet Summary Report contents. As you solution increases in complexity, you may want to explore Control Center as the most logical expansion.

Cheers!

Julio