Custom Serial Numbers


Hi there!

I want to be able to assign a unique serial number to each newly populated row. One of the columns, "Type", determines the first letter of the serial number for the respective row - the prefix - and the suffix is a three digit number (starting at 001) that increments by 1 determined by the prefix. For example:

In each of these, selecting the validated inputs via dropdown should determine the first letter of the "REF ID" and this formula determines the remaining number:

=LEFT(Type@row) + "-" + RIGHT(1000 + COUNTIFS(Type$1:Type@row, Type@row), 3)

And this works beautifully, so long as the rows aren't re-sorted or filtered. The problem is that the unique IDs assigned by this method don't stay assigned to their original rows as the absolute reference moves with the sort, and consequently these IDs don't stay consistent to a row. And there doesn't seem to be an automation path that assigns a calculated value to a cell.

It's often discussed on here, but I hadn't seen a post that called out the problem of Filter / Sort.

Would welcome input. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!