# 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

• ✭✭✭✭✭

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!

• ✭✭
edited 09/12/22

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!