Auto Number Column is Being Copied Over From New Rows

Sarah123

I have a row ID setup with auto numbering on a totals sheets that contains all the copied rows from multiple projects. When the copied rows are inputted into the total sheets (using automation) the row ID is then updated with the row ID from the other sheets. I use this auto number field to identify duplicate rows (and in turn remove those rows). Is there a way to create another auto numbering column that I could use for my duplicate formula? Or a way to stop the overwriting of the auto number column? Right now it's deleting all the multiple rows (instead of just the duplicated ones). The formula I use is to identify the duplicates is =IF(COUNTIFS([Task ID]:[Task ID], [Task ID]@row, [Row ID]:[Row ID], @cell <= [Row ID]@row) > 1, 1, 0) .

Sarah123

Turns out the functionality of copying over the auto number is working as designed (must've been lucky with it being broken before lol). Instead I ended up using a copied into column and updated my formula to look at that column instead of the row auto number.

Andrée Starå

Hi @Sarah123

I hope you're well and safe!

You could use this workaround only to include what you need/want.

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!

Sarah123

That would work but we will get up there with the number of links allowed within Smartsheet pretty quick. Is there a way to create a column formula to add 1 to the cell number above? I could make a top row formula to have 1 in the cell and then have all cells below it reference the cell above it and add 1. But everytime I try that it won't work to create a column formula because it's referring to a specific cell. I tried to find a way to add up a helper column that contains 1 in each row and to add up that @row and above in the helper column to create the row ID but I can't seem to figure out a formula to do that either.

Sarah123

Turns out the functionality of copying over the auto number is working as designed (must've been lucky with it being broken before lol). Instead I ended up using a copied into column and updated my formula to look at that column instead of the row auto number.

Andrée Starå

@Sarah123

Excellent!

Glad you got it working!