Automate data flow from Source to Target sheet (based on New & Edit criteria)

PKotha · 07/28/22

Looking to automate data flow from Source to Target sheet based on below criteria

New row: If new row added in source sheet then new row should be added to target sheet. I should able to config what columns need to move target sheet.

Update/Edit: if any row get updated in source sheet then corresponding row in target sheet should get updated if the primary key of source and target sheet matches. I should able to config what columns need to get updated.

Is it possible to automate above criteria, I looked into "Data Shuttle" and sheet automation features but no luck.

Thank you in advance.

Amit Wadhwani · 07/28/22

HI @PKotha

I should able to config what columns need to move target sheet.

Unfortunately, when you apply copy/move automation, all the columns are moved to the destination. Here is a workaround that I would propose.

Create an automation which copies all the new rows (including all the columns) into the destination sheet.
All the columns in the destination sheet, except the primary key should be hidden.
Create new columns with same name as the hidden columns (with some variation as Smartsheet does not allow duplicate column names). You can limit the new columns to only those which you desire to be shown on the second sheet.
In these columns, apply INDEX/MATCH formula to reference data from the source sheet based on the primary key.

I hope this helps.

Best Regards

Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

https://www.linkedin.com/in/amitinddr/

Andrée Starå · 07/28/22

Hi @PKotha

I hope you're well and safe!

To add to Amit's excellent advice/answer.

Another option (depending on your structure and specifics) would be to only use cross-sheet formulas (VLOOKUP or INDEX/MATCH), so there is no need to copy the rows.

You'd use an auto number column in the source sheet and then add a so-called helper column in the destination sheet where you'd add the number manually, and we use that to connect the rows.

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!

PKotha · 07/28/22

Thank you Amit and Andree. Let me try both options and I will update how it went.

Andrée Starå · 07/28/22

@PKotha

Happy to help!

✅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!

Automate data flow from Source to Target sheet (based on New & Edit criteria)

Answers

Help Article Resources

Categories