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([email protected]) +“-”+RIGHT(1000 + COUNTIFS(Type$1:[email protected],[email protected]), 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 ✭✭✭✭✭✭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([email protected]) +“-”+[email protected]
You can always add summary columns to keep a running count of each type of row.
Regards,
Jeff Reisman,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US
链接:Smartsheet Functions Help Pages链接: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 ✭✭✭✭✭✭@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([email protected]) +“-”+IF(COUNTIFS(Type:Type, @cell =[email protected], Auto:Auto, @cell<=[email protected])< 10, "00", IF(COUNTIFS(Type:Type, @cell =[email protected], Auto:Auto, @cell<=[email protected])< 100, "0")) + "" +COUNTIFS(Type:Type, @cell =[email protected], Auto:Auto, @cell<=[email protected])
thinkspi.com
-
Jeff Reisman ✭✭✭✭✭✭
Well damn, that's just brilliant.
@Lisette PlankenI have a nomination⬆️for "Formula of the Week!"
Regards,
Jeff Reisman,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US
链接:Smartsheet Functions Help Pages链接: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 ✭✭✭✭✭✭
@Jeff ReismanHahaha. Is "Formula of the Week" an actual thing??
thinkspi.com
-
Jeff Reisman ✭✭✭✭✭✭
Yes!I get an email every Tuesday from Lisette. Maybe I'm just special.
Regards,
Jeff Reisman,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US
链接:Smartsheet Functions Help Pages链接: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 ✭✭✭✭✭✭
@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.
thinkspi.com
-
Andrée Starå ✭✭✭✭✭✭@Jeff ReismanNice 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:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
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 =[email protected], Auto:Auto, @cell<=[email protected])+ 999
If you want to use different numbers for different types, you would use an IF or nested IF.
COUNTIFS(Type:Type, @cell =[email protected], Auto:Auto, @cell<=[email protected])+ IF([Project Type]@row = "A", 999, IF([Project Type]@row = "B", 546, 0))
thinkspi.com
-
@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.
![\"Screenshot](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/Z74WB51LFP32\/screenshot-2023-06-28-at-7-56-19-am.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
