Is there an automation/formula that allows a copied row to be updated?

lmmayer

I have created a master sheet for grant invoicing. We have 14 different institutions who received the grant and who will be submitting invoices to the master sheet. I have created a workflow for each institution so that when they submit to the master sheet, that row will be copied to a separate Smartsheet with just the info for their institution. There is a row for invoice status on the master sheet that can be manually updated to "received, submitted for payment, paid" depending on where that invoice is in the process. I'm trying to have the copied row update in the separate sheet when I update the row in the master sheet. Is that possible?

Paul Newcome

If you have a unique identifier for each row in the master that copies over, you can use that for an INDEX/MATCH.

But is there a reason you are not using a row report?

lmmayer

Paul, my knowledge base for Smartsheet it very basic so could you please expand on the row report?

Nick Korna

A row report combines 1 or more sheets into a single report.

In this case, you could make a template of the sheet and give each institution their own sheet which they can submit the invoices to & view at any time. The row report can then look at each sheet and combine them into a single report which will have all the data in real time. The report can display as much or as little of the data (i.e. which columns) as you wish, so you don't necessarily have to see everything at once.

If you're needing to update the data as well, the row report lets you click through to the relevant sheet to make the updates.

The link below should help explain/demonstrate:

https://help.smartsheet.com/learning-track/smartsheet-intermediate/reports

Hope this helps, but if you're still unsure of something then just ask! 😊

lmmayer

Nick, thank you! That is very helpful! Is it possible to just share the report with each institution that shows only their data? Just trying to think through the easiest way to go about it.

For ease for the institutions (and for us), we were going to provide the link to the master sheet where invoices could be uploaded and then I was planning to have each institutions rows copied to individual sheets from there. I would update the invoice status on the master sheet and wanted it to also update to the copied row on the institution's sheet.

Paul Newcome

Since you already have the master and are providing them access to that for new entries. You can create a row report for each institution. You can create a filter in the report to only show the rows that are applicable to them.

These row reports would replace your copy sheets and as the report is updated, the master sheet will be too (and the other way around as well).