Automatic ticket numbering by location
Hello. I worked with someone on a previous discussion, but for some reason cannot locate it now. I have one data base file with the work orders from many different community locations. I wanted a solution for the Work Order Number to be a combination of the community abbreviation, the year, and then the number of work order for that year. This worked great until 2020 rolled around and I can see now that everything works except for the last part; it did not restart the numbering for this year. Because of this, it changed the year within the work order, but it simply continued on the numbering from 2019.
I have provided the formula below. Within it, the "Community Code" is the abbreviation and the "Request Date" is the date a request was submitted. I've also included a picture in case my explanation of the issue doesn't suffice.
=(社区代码)@row +“-”+年(@请求日期row) + "-" + RIGHT(10000 + COUNTIF([Community Code]$1:[Community Code]1, [Community Code]@row), 4)
Any help with this is greatly appreciated.
Thanks.
Best Answer
-
Paul Newcome ✭✭✭✭✭✭My apologies. Missed a closing parenthesis...
=(社区代码)@row +“-”+年(@请求日期row) + "-" + RIGHT(10000 + COUNTIFS([Community Code]$1:[Community Code]@row, [Community Code]@row, [Request Date]$1:[Request Date]@row, YEAR(@cell) = YEAR([Request Date]@row)), 4)
That was also the issue with the first formula I provided.
thinkspi.com
Answers
-
Paul Newcome ✭✭✭✭✭✭
Where are new rows added? Are they always added at the bottom of the sheet?
thinkspi.com
-
M. David ✭✭✭✭✭
Yes. They are always added at the bottom.
-
Paul Newcome ✭✭✭✭✭✭
Ok. Then we should be able to take your original formula and add a condition to the COUNTIFS that will include the year.
=(社区代码)@row +“-”+年(@请求日期row) + "-" + RIGHT(10000 + COUNTIFS([Community Code]$1:[Community Code]1, [Community Code]@row, [Request Date]$1:[Request Date]1, YEAR(@cell) = YEAR([Request Date]@row), 4)
thinkspi.com
-
M. David ✭✭✭✭✭
Hey. I tried that out and it is returning an error message. It is an "Incorrect Argument Set" error. Any ideas what could cause this? I tried to follow the path on the new range and criteria you added, but didn't see what is causing the error. Thanks.
-
Paul Newcome ✭✭✭✭✭✭
Did you change COUNTIF to COUNTIFS (with the S on the end)?
thinkspi.com
-
M. David ✭✭✭✭✭
Yes. I typed it in. I did just notice an extra ")" I left in. When I took that out, no longer received an error message, but the formatting was incorrect. It dropped the 4 digits at the end and made it simply a one digit code. I copied this formula from row one into the area where I knew the years crossed over from 2019 to 2020 and updated the row references. After this, I dragged it down and have a very interesting result. It randomly shows the first number as a "7". It then restarts the numbering. See the picture below. If possible, I'd like to keep the four digits, so ticket number one would be CODE-2020-0001.
Thanks for you help with this. I do appreciate the help.
-
Paul Newcome ✭✭✭✭✭✭
It should not have dropped the leading zeros. Those leading zeros actually come from the bold part below
RIGHT(10000 +COUNTIFS(...................), 4)
Put this in row 1 and dragfill down, then take a look at an area that shows a year change.
=(社区代码)@row +“-”+年(@请求日期row) + "-" + RIGHT(10000 + COUNTIFS([Community Code]$1:[Community Code]@row, [Community Code]@row, [Request Date]$1:[Request Date]@row, YEAR(@cell) = YEAR([Request Date]@row), 4)
thinkspi.com
-
M. David ✭✭✭✭✭
I did this and it shows the Incorrect Argument Set error again.
-
Paul Newcome ✭✭✭✭✭✭
My apologies. Missed a closing parenthesis...
=(社区代码)@row +“-”+年(@请求日期row) + "-" + RIGHT(10000 + COUNTIFS([Community Code]$1:[Community Code]@row, [Community Code]@row, [Request Date]$1:[Request Date]@row, YEAR(@cell) = YEAR([Request Date]@row)), 4)
That was also the issue with the first formula I provided.
thinkspi.com
-
M. David ✭✭✭✭✭
Eureka! That solved the problem. I always start by looking at parenthesis when there's a problem but I missed it as well. Thank you for all of your help with this. I really appreciate it.
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
=(NETDAYS([Requested Start Date]@row, DATE(2022, 12, 1)<\/strong>) \/ 365) * 12<\/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":107082,"type":"question","name":"Automatic Update of Dropdown List?","excerpt":"I saw several discussions on this and I'm wondering if there is a formula or an easy way without a 3rd party to automatically update a dropdown list based on a cell in one sheet? I can create a helper sheet if that would help. Any ideas? I don't believe I have the premium Smartsheets plan. Thank you! Lori","snippet":"I saw several discussions on this and I'm wondering if there is a formula or an easy way without a 3rd party to automatically update a dropdown list based on a cell in one sheet?…","categoryID":322,"dateInserted":"2023-06-29T14:16:13+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T17:27:22+00:00","insertUserID":162337,"insertUser":{"userID":162337,"name":"maineL","title":"Admin. & Course Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/maineL","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!plNXqQdBs_o!2kvQ_7y42f4!PTHvB33mICN","dateLastActive":"2023-06-29T17:26:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":162337,"lastUser":{"userID":162337,"name":"maineL","title":"Admin. & Course Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/maineL","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!plNXqQdBs_o!2kvQ_7y42f4!PTHvB33mICN","dateLastActive":"2023-06-29T17:26:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":42,"score":null,"hot":3376113815,"url":"https:\/\/community.smartsheet.com\/discussion\/107082\/automatic-update-of-dropdown-list","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107082\/automatic-update-of-dropdown-list","format":"Rich","lastPost":{"discussionID":107082,"commentID":383165,"name":"Re: Automatic Update of Dropdown List?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383165#Comment_383165","dateInserted":"2023-06-29T17:27:22+00:00","insertUserID":162337,"insertUser":{"userID":162337,"name":"maineL","title":"Admin. & Course Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/maineL","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!plNXqQdBs_o!2kvQ_7y42f4!PTHvB33mICN","dateLastActive":"2023-06-29T17:26:49+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-29T14:52:00+00:00","dateAnswered":"2023-06-29T14:50:26+00:00","acceptedAnswers":[{"commentID":383100,"body":"
