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
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!