2 system generated auto number columns

Chris G

Hi Team,

I have sheets from different departments that have auto generated numbers in them. What I’m wanting is when the sheets are copied to a master sheet, I want to have another system generated number as a unique code. But I can’t get it to work. I need each department to have its auto number, then once moved to the master sheet, another system number is generated, before the rows are moved again.

Is there a work around for this?

Cheers

Chris

jpaul

Have you tried changing the column properties in the master sheet for when the auto department number is moved over into just a text column? That would allow the sheet to have an auto numbered column again. How are you copying the the rows over? Automation, Data Mesh, Data Shuttle?

Chris G

Thank you for reply.

I am using automation.

The system generated columns always refer to each other when copying, I have tried multiple ways of renaming and text change.

jpaul

Chris, do you have access to the advanced Smartsheet functions like Mesh and Shuttle?

Andrée Starå

Hi @Chris G

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.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

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!

Chris G

@jpaul I will look into Shuttle and Mesh, I'm not familiar with them.

@Andrée Starå I will work out if that could work to suit my needs. I like cross sheet formulas but we often reach the limitation quickly.

Andrée Starå

@Chris G

Excellent!

Here's more information about Data Shuttle and DataMesh.

https://www.smartsheet.com/marketplace/premium-apps/data-uploader

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

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!