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!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 09/14/22

    @Kyle Jaster

    If you want to assign unique IDs that stay with the row, you would need to use an Auto-number system column. Unfortunately that means you wont be able to have an A-001 and an I-001, etc., but your numbers will be unique and will stay with the row no matter how you sort. Instead you'd have on row 1 "A-001," on row 2 "I-002," on row 3 "R-003," etc.

    =LEFT(Type@row) + "-" + AutoNumber@row

    You can always add summary columns to keep a running count of each type of row.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 09/14/22

    @Jeff Reisman I do this pretty regularly, but instead of just pulling in the Auto-Number, I count how many are less than or equal to which allows me to have

    A - 001

    A - 002

    I - 001

    I - 002

    A - 003


    Note: The IF statements are for the zero fill.

    =LEFT(Type@row) + "-" + IF(COUNTIFS(Type:Type, @cell = Type@row, Auto:Auto, @cell<= Auto@row)< 10, "00", IF(COUNTIFS(Type:Type, @cell = Type@row, Auto:Auto, @cell<= Auto@row)< 100, "0")) + "" + COUNTIFS(Type:Type, @cell = Type@row, Auto:Auto, @cell<= Auto@row)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    Well damn, that's just brilliant.

    @Lisette Planken I have a nomination ⬆️ for "Formula of the Week!"

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Jeff Reisman Hahaha. Is "Formula of the Week" an actual thing??

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    Yes! 😁 I get an email every Tuesday from Lisette. Maybe I'm just special.


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Jeff Reisman NICE!!


    @Lisette Planken How can I get in on this? Also... Please don't get Jeff in trouble for sharing something secret. Haha. Also also... I use something very similar to auto-format based on hierarchy except I wrap the ANCESTORS function in an IF statement to only output on parent rows. Blank rows don't get any formatting. Rows that aren't blank get formatting based on the number.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Jeff Reisman Nice x2!

    @Lisette Planken, I'd like to be part of the secret society too! 🧙‍♂️🤣

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • @Paul Newcome

    This is absolutely brilliant! I was hoping you could help me take this a step further... The original poster opted to start his unique serial numbers from '001.' Would there be a way to get your proposed solution working for serial numbers starting at different values?

    For example, let's say that the original poster already has serial numbers A-001 through A-999 assigned in some legacy system that they do not wish to track or maintain in the new sheet they just constructed. How could they pick up where they left off with the "A" type without manually adding 999 rows with type "A" assigned?

    If that wasn't challenging enough, would it be possible to have the "B" type start off on its own separate value and assign sequential serial numbers from then on out? Let's say the original poster left off with A-999 and B-546 in the legacy system. Could they easily pick up in the new sheet and have A-1000 and B-547 to be the first newly assigned unique serial numbers?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Erica Pinkus You would just "add" 999 to the COUTNIFS.

    COUNTIFS(Type:Type, @cell = Type@row, Auto:Auto, @cell<= Auto@row) + 999


    If you want to use different numbers for different types, you would use an IF or nested IF.

    COUNTIFS(Type:Type, @cell = Type@row, Auto:Auto, @cell<= Auto@row) + IF([Project Type]@row = "A", 999, IF([Project Type]@row = "B", 546, 0))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome Is it possible to take this another step further?

    On top of the ask I posted on May 11th, I am wondering if it is possible to set up a second helper column with a nested IF statement that can add one letter suffixes to any of the previously generated lot numbers?

    In some situations, for example, I would need A-1000 to be broken down into A-1000a, A-1000b, A-1000c, …, A-1000j. I was thinking about having the end user select yes/no for a column that would trigger the addition of the next lower case letter in alphabetical order that somehow conserves the "A-1000" portion of the assigned lot number in the same row. After those new lot numbers are generated (one lot number per row) the user would have the option to select "no" for the column triggering the addition of those one-letter suffixes on the next empty row, and the sheet would go back to assigning the next sequential lot number according to the category they select. I will illustrate below using a dummy Excel table:

    I realize that this probably is unlikely, so if you have any ideas on how to still get lower case letters to be added to a conserved lot number when the end user wants to do so (i.e. by selecting "yes in the "Selectivity Samples" column) by some other automatic workaround or manual one, I would love to hear them. Please note, however, I need the assigned lot numbers to remain assigned to a consistent row should the sheet be sorted or filtered. The other constraint is that the average user of this sheet won't have a license, so they wouldn't be able to leverage those functionalities that need a license.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!