Automatic ticket numbering by location

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.


=[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.

Best Answer

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

  • Yes. They are always added at the bottom.

  • Paul NewcomePaul 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)

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    Did you change COUNTIF to COUNTIFS (with the S on the end)?

  • 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 NewcomePaul 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)

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

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

Sign In or Register to comment.