Copying rows and updating the cell contents as changed to a back-up sheet

Mounika

Hi all,

I have a sheet, which captures data as the form gets submitted by the participant. So, for this sheet (let's call it as source sheet) I would like to create an other back-up sheet (just in case if the data gets deleted or something happens unexpectedly) which stores all the data from source sheet. And if the cells are updated in the source sheet, I want the same cells to updated automatically back in the back-up sheet.

I have a tried using a "Copy a row to other sheet, when specific criteria are met", but this workflow does not seem to work in my case, as I also need the changes made in source sheet cells to reflected back in backup sheet, without having the copy of entire row again.

In a nutshell, I am looking for a workaround that would copy all cell contents from source sheet to destination sheet, and any changes/ updates made in source sheet needs to reflect back into the back-up sheet.

Can someone please help find a solution for this? Looking forward for the valuable suggestions.

Thanks,

Mounika

Andrée Starå

Hi @Mounika

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

To connect them row by row, you could use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you need in the Destination sheet.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Mounika

Hi @Andrée Starå

Thank you so much for the response. I will try it and get back to you!

Mounika

Hi @Andrée Starå

Either VLOOKUP/ index-match will need a common column to match both the sheets, however, in my case, my destination sheet is empty, with all same column names as in Source sheet (which also has the data coming in from another sheet (columns highlighted in red box). And I want all the data from this source sheet to be stored as a back-up in another sheet (i.e destination sheet in this case). If in case, the submitted data is edited/updated (in source sheet) that changes need to be reflected back in the destination sheet.

Paul Newcome

This is a good use case for the premium add-on Data Mesh. Any formulas will have live data and will be removed from the copy sheet if they are removed from the source sheet.

Andrée Starå

@Mounika

You could still use the sheet as long as we can add the formulas needed.

Another option would be the premium app, Data Mesh. Is that an option?

https://www.smartsheet.com/marketplace/premium-apps/datamesh

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!