Auto Generate unique ID in a column incremented from the previous max number in that column.

PEARCEMI

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

TVang

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!

PEARCEMI

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.