Incrementing last number without AutoNumber?

JPJPJP · 01/04/22

Hi there,

Is there a way when a new row is added, to increment the ID number from the previous row without using AutoNumber?

We have Project ID's that can't risk being changed (i.e. if someone accidently deletes a row and all the autonumbers shift), and sometimes certain rows might be moved to an 'Archive' sheet where it needs to retain the ID number.

2nd question - Is there a way to have a separate 'metadata' helper sheet, that keeps track of the latest number, since the row can move between sheets? That way any new rows use that latest number and then updates the metadata sheet every time?

Paul Newcome · 01/04/22

Using the Auto-number type column, the numbers should never be repeated. If #15 is deleted or moved to another sheet, then the auto-number column should not output a second #15.

Andrée Starå · 01/04/22

Hi @JPJPJP

Yes, and one way could be to use a so-called helper sheet to assign the number, and then it's locked to that row.
Yes, you could use the same sheet to show the latest number.

Make sense?

Would that work/help?

I hope that helps!

Have a fantastic week & Happy New Year!

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

Paul Newcome · 01/04/22

Using the Auto-number type column, the numbers should never be repeated. If #15 is deleted or moved to another sheet, then the auto-number column should not output a second #15.

JPJPJP · 01/04/22

https://community.smartsheet.com/discussion/comment/314762#Comment_314762

Ah - so you're saying even if the say there's 20 items, and the last item gets deleted/moved. The next row will still be #21?

The only way the numbers shift is if the AutoNumber is manually refreshed?

Maaik Meijerink · 01/04/22

Hello @JPJPJP,

On of the reasons for using AutoNumber (Column Type: Auto-Number/System ) is actually to prevent the number is reused if someone accidently or on purpose deletes a row. So Autonumbers do not shift, but retain the same ID as originally was given to.

So your question about not using AutoNumber because the autonumbers will shift, does not make sense to me. Perhaps you mean something else, rownumber maybe which indeed will shift when deleting rows?

Regarding your second question: of course you can use a seperate sheet metadata which will keep track of the latest number (of both sheets: original and Archived). using the MAX in combination with Reference to the original and Archive sheet.

However any new rows added to the original sheet however do not need to 'look' at this Metadata sheet as all ID's are originated from the original sheet and therefor already unique. Perhaps you can look at my answer for your first question again to see if this metadata sheet is still needed ?

JPJPJP · 01/04/22

Thanks @Andrée Starå @Paul Newcome @Maaik Meijerink !

Sorry for the confusion. I don't know why I thought the AutoNumbers shifted! I just tested it, and you're right. They only shift when you manually reset the column it seems, so in this case a helper sheet wouldn't be needed... phew!!

Thanks for clarification!

Andrée Starå · 01/04/22

@JPJPJP

You're more than welcome!

No worries!

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

Paul Newcome · 01/04/22

Happy to help. 👍️

JPJPJP · 01/05/22

https://community.smartsheet.com/discussion/comment/314757#Comment_314757

Awesome do you have an example, or formula, or a link to some documentation on how to do that?

I can't seem to figure out how to update/increment the value in the helper sheet.

Donovan · 04/28/23

@Paul Newcome unless you are using an automation to copy rows into the sheet, they then come with the source Auto number and will be duplicated

Incrementing last number without AutoNumber?

Best Answer

Answers

Categories