Custom Serial Numbers

Kyle Jaster

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!

Jeff Reisman

@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.

Paul Newcome

@Jeff ReismanI 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)

Jeff Reisman

@Jeff Reisman<\/a> 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<\/p>

A - 001<\/p>

A - 002<\/p>

I - 001<\/p>

I - 002<\/p>

A - 003<\/p>

Note: The IF statements are for the zero fill.<\/p>

=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)<\/strong><\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jeff Reisman\",\"userID\":86866}}},{\"insert\":\" 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\\nA - 001\\nA - 002\\nI - 001\\nI - 002\\nA - 003\\n\\nNote: The IF statements are for the zero fill.\\n=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\\\")) + \\\"\\\" + \"},{\"attributes\":{\"bold\":true},\"insert\":\"COUNTIFS(Type:Type, @cell = Type@row, Auto:Auto, @cell<= Auto@row)\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2022-09-14T13:29:56+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-20T03:23:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/343477#Comment_343477","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/343477#Comment_343477

Well damn, that's just brilliant.

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

Paul Newcome

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

Jeff Reisman

@Jeff Reisman<\/a> Hahaha. Is "Formula of the Week" an actual thing??<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jeff Reisman\",\"userID\":86866}}},{\"insert\":\" Hahaha. Is \\\"Formula of the Week\\\" an actual thing??\\n\"}]","format":"rich","dateInserted":"2022-09-14T16:19:30+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-20T03:23:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/343526#Comment_343526","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/343526#Comment_343526

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

Paul Newcome

@Jeff ReismanNICE!!

@Lisette PlankenHow 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.

Andrée Starå

@Jeff ReismanNice x2!

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

Erica Pinkus

@Paul Newcome

@Jeff Reisman<\/a> 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<\/p>

A - 001<\/p>

A - 002<\/p>

I - 001<\/p>

I - 002<\/p>

A - 003<\/p>

Note: The IF statements are for the zero fill.<\/p>

=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)<\/strong><\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jeff Reisman\",\"userID\":86866}}},{\"insert\":\" 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\\nA - 001\\nA - 002\\nI - 001\\nI - 002\\nA - 003\\n\\nNote: The IF statements are for the zero fill.\\n=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\\\")) + \\\"\\\" + \"},{\"attributes\":{\"bold\":true},\"insert\":\"COUNTIFS(Type:Type, @cell = Type@row, Auto:Auto, @cell<= Auto@row)\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2022-09-14T13:29:56+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-20T03:23:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/343477#Comment_343477","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/343477#Comment_343477

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?

如果这不够有挑战性,会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

@Erica PinkusYou 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))

Erica Pinkus

@Paul NewcomeIs 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.