Auto Generate unique ID in a column incremented from the previous max number in that column.
I am working on an asset sheet where I need to generate unique ID numbers continuing from an existing sequence. The final ID will have one of a few letter prefixes + 4 digit number. The sheet will be populated by a form through which new assets that need ID numbers as well as existing assets that already have ID numbers will be entered. I am trying to figure out the best way to make sure the new assets receive the next incremental number in the sequence with the appropriate letter. I have tried a few things, but keep getting circular reference. Below is an example of what I am trying to accomplish. Any help would be greatly appreciated.
Thanks
Answers

Hi, @PEARCEMI
Create a column/field to record the number if one exists (e.g., "Existing Asset Number").
Use the formula below to create the "Asset Number":
=[Letter Code]@row + "" + IF([Existing Number]@row = "Yes", [Existing Asset Number]@row, (1500 + COUNTIFS([Existing Number]:[Existing Number], "No", AutoNumber:AutoNumber, <=AutoNumber@row)))
The expression,
COUNTIFS([Existing Number]:[Existing Number], "No", AutoNumber:AutoNumber, <=AutoNumber@row)
, returns the number of rows that do not have an existing number and have a value in "AutoNumber" that is less than or equal to that of the current row. Adding "1500" to this count gives you the current MAX number in the sequence.Cheers!

Thanks @Toufong Vang! I made some small mods to that and it seems to be working as long as I substitute the current last used number in the sequence for the 1500 and start the sheet from there. I appreciate the help.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!