Generate the next case number

[julyjustice]

Good evening
I need Excel to generate the next case number in a series where the format follows a pattern of nnn.xxxx.YYYY_Text. The nnn is selected from a specific and finite list of numbers depending on the type of case. For example, 100, 112, 311, 404, 500, 510. The xxxx is the segment that needs to get incremented by one each time a number is being assigned. The xxxx started at 0001, with the next case being 0002, etc. The YYYY is the 4-digit year in which the case is assigned. The _Text is a short text string related to the name of the case. Is there a formula that could accomplish the task of assigning the next number in the series, not including the _Text part which would get entered by the user when the new number is needed?

 

[jimrward]

are you able to provide a sample of your data are the values for nnn xxxx YYYY in separate cells with possibly nnn from a drop down list

 

[popipipo]

If you can use 3 columns this is a way to do it.

 

[julyjustice]

are you able to provide a sample of your data are the values for nnn xxxx YYYY in separate cells with possibly nnn from a drop down list

Here are a few samples:

100.0001.2020_ShortText
100.0002.2020_DifferentShortText

112.0001.2020_Text

311.0001.2020_Text

At this point, the segments of the number are not in separate columns, although I have thought about doing it that way. And based on the reply from popipipo, I am considering using separate columns for each part of the number. For each category of case number, which determines the 3 digits at the beginning, those 3 digits are always the same in that category. And for each category, the 4 digits that are the second segment start at 0001 and increment by one each time. The year segment stays the same for the entire fiscal year. The short text is the least important segment but gets used for the convenience of the person handling the case so that they can more easily and quickly recognize the matter. I started by using one worksheet, sorting by the Case number which results in the list being in order by category so that I could insert a new row at the end of each category each time a new assignment is made and assign the new number by manually incrementing the number on the row above. Unfortunately, that has resulted in a few errors. I have figured out how the errors happened and almost all are 'operator error' - the operator being me. I want to set up a method of automatically getting the next correct number to prevent that for the future. So far, I have several new possibilities now based on the other reply, but if you have an even better method, I am open to any help you might provide.

Thanks so much for your time in considering this question.

 

[julyjustice]

If you can use 3 columns this is a way to do it.

Thanks so much for your time in responding to this question. I had thought about using separate columns and your suggestion is making me think that is probably going to be the way to go.