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:

Screen Shot 2022-09-13 at 9.29.19 PM.png

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册template!
Hi @Sam Swain<\/a>,<\/p>

You should be able to use this formula to accomplish this:<\/p>

=IF(HAS([Type of Project]@row, \"Consolidation\"), \"🝢\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Reduction\"), \"︾\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"New\"), \"○\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Termination\"), \"⨷\", \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>

Example output:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps - if there are any issues etc. then just post! ☺️<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106930,"type":"question","name":"Help with date formate","excerpt":"I have my months extracted from a date column using =Left(EISD@row,2) I need a formula to evaluate my month column, if it's 01-06 it returns an S, if its 07-12 (or <=7) then it returns an F.","categoryID":322,"dateInserted":"2023-06-26T22:42:11+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:53:54+00:00","insertUserID":157924,"insertUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":157924,"lastUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":24,"score":null,"hot":3375690965,"url":"https:\/\/community.smartsheet.com\/discussion\/106930\/help-with-date-formate","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106930\/help-with-date-formate","format":"Rich","lastPost":{"discussionID":106930,"commentID":382517,"name":"Re: Help with date formate","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382517#Comment_382517","dateInserted":"2023-06-27T12:53:54+00:00","insertUserID":157924,"insertUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T12:53:24+00:00","dateAnswered":"2023-06-26T23:07:25+00:00","acceptedAnswers":[{"commentID":382460,"body":"

Assuming your result will only ever be 01-12, this should do the trick. The VALUE() function may not necessarily be needed, but I'm not sure what type of date you are pulling the month information from and it isn't going to hurt anything.<\/p>

=IF([Month Column]@row <> \"//www.santa-greenland.com/community/discussion/comment/\", IF(VALUE([Month Column]@row) < 7, \"S\", \"F\"))<\/p>

Replace [Month Column] with your column name.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106913,"type":"question","name":"SUMIF with a checkbox","excerpt":"I'm trying to sum a row of amounts (Parking Revenue Regular:Private boat parking revenue) into the column \"Total Parking Revenue\", but only if the checkbox \"Payment Voucher\" isn't checked. When I add the rows using this formula without the checkbox, it works (formula on first row only): =SUM([Parking Revenue…","categoryID":322,"dateInserted":"2023-06-26T18:20:45+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T19:03:27+00:00","insertUserID":162776,"insertUser":{"userID":162776,"name":"declark","title":"Regional Director","url":"https:\/\/community.smartsheet.com\/profile\/declark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4cFWuLGjTPw!x-tXMZGznvw!Dlin9vWXHb9","dateLastActive":"2023-06-27T14:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162776,"lastUser":{"userID":162776,"name":"declark","title":"Regional Director","url":"https:\/\/community.smartsheet.com\/profile\/declark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4cFWuLGjTPw!x-tXMZGznvw!Dlin9vWXHb9","dateLastActive":"2023-06-27T14:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":33,"score":null,"hot":3375611052,"url":"https:\/\/community.smartsheet.com\/discussion\/106913\/sumif-with-a-checkbox","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106913\/sumif-with-a-checkbox","format":"Rich","lastPost":{"discussionID":106913,"commentID":382400,"name":"Re: SUMIF with a checkbox","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382400#Comment_382400","dateInserted":"2023-06-26T19:03:27+00:00","insertUserID":162776,"insertUser":{"userID":162776,"name":"declark","title":"Regional Director","url":"https:\/\/community.smartsheet.com\/profile\/declark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4cFWuLGjTPw!x-tXMZGznvw!Dlin9vWXHb9","dateLastActive":"2023-06-27T14:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/3KE67LQDZX4M\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T19:03:34+00:00","dateAnswered":"2023-06-26T18:31:26+00:00","acceptedAnswers":[{"commentID":382384,"body":"

Try IF([payment voucher]@row=0,Sum([Parking Revenue Regular]@row:[Private boat parking revenue]@row),\"//www.santa-greenland.com/community/discussion/comment/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions