Automatic ticket numbering by location

M. David

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.

=[Community Code]@row + "-" + YEAR([Request Date]@row) + "-" + RIGHT(10000 + COUNTIF([Community Code]$1:[Community Code]1, [Community Code]@row), 4)

Any help with this is greatly appreciated.

Thanks.

Paul Newcome

My apologies. Missed a closing parenthesis...

=[Community Code]@row + "-" + YEAR([Request Date]@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.

Paul Newcome

Where are new rows added? Are they always added at the bottom of the sheet?

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.

=[Community Code]@row + "-" + YEAR([Request Date]@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)

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)?

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.

=[Community Code]@row + "-" + YEAR([Request Date]@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)

M. David

I did this and it shows the Incorrect Argument Set error again.

Paul Newcome

My apologies. Missed a closing parenthesis...

=[Community Code]@row + "-" + YEAR([Request Date]@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.

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

Happy to help! 👍️