Is there a way to automate a row copy and have the row link back to the original cells?

Tony Rojek

I'd like to rows, when changed in one sheet to copy to another sheet on a specific trigger, but once copied I'd like the cells to link back to the cells in the original sheet so any changes to the row in the destination sheet is reflected on the original sheet. Is there a way to automate this, or would the link have to made manually every time?

Andrée Starå

Hi @Andrew Watts

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?

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!

Michaela Kamenska

Hi Tony,

there is an option to automatically copy rows to another sheet once certain criteria are met under the Automation tab. What you can then do is create a e.g. INDEX column formula and the cells will automatically change if the original cell in the sheet changes. However, this does not work both ways. You won't be able to change cells in the secondary sheet. Alternatively consider creating a row report.

Andrée Starå

Hi @Tony Rojek

I hope you're well and safe!

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

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 help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrew Watts

Hey @Andrée Starå

Can you provide an example of how you would set up your idea?

I am looking to have a 'Pre-intake sheet' before my project is provisioned using control center.

I would like the data that the pre-intake sheet collects to automatically copy across to the intake sheet in a new row plus update when data changes in the pre-intake sheet. Only downstream data changes allowed.

Possible?

Thanks in advance.

Andrew Watts

Hey @Andrée Starå,

Just wondering if you can help at all with my above question?

Thanks in advance

Andrew

Andrée Starå

Hi @Andrew Watts

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?

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!

Andrew Watts

Thank you! Worked a treat!