Dropdown List with Letters, Numbers, and Formulas

[YoloSpaceMuffin]

Ok, so this is a little hard to explain.

So I've created a dropdown list that will contain the letters C, G, S, L, and E to represent types of drawings. (i.e., G is a general drawing). Each drawing has a code number and range: G-001 to G-099; C-100 to C-109 (this is for coversheet drawings); C-110 to C-199 (this is for profile drawings); S-500 to S-599; L-700 to L-799; E-800 to E-899. These numbers have to stay in these ranges! They help identify what type of drawing it is as well!

I want to have it so that when a drawing type is chosen from the dropdown list, it will show '-001' (i.e., G-001); and if it is selected again, it would increase to '-002' and so on.

Is there any formula that can do that?

[#2]

@YoloSpaceMuffin 

I was trying to understand what you have written. It seems you must have many separate tables/list.

[#3]

@jitinm 

I'm not the best at explaining things when it comes to Excel lol. Here's an updated version of what I have. The 'Code' column (column b) just doesn't go down to the next code. i.e., A2="general" so B2=G-001; when A12="general", B12 SHOULD equal G-002, but instead it shows G-001 again. Hopefully, this makes more sense!

A quick note about my picture. Columns F to O are the drawing codes for each drawing type. Columns F and H to O (so not column G) go up to 1 below the next column's first number. i.e., profile is C-110 to C-199, and section is C-200 to C-299. (If you need more clarification let me know!)

[#4 · Accepted solution]

@YoloSpaceMuffin 

=INDEX($F$2:$O$35,COUNTIF($A$2:A2,A2),MATCH($A2,$F$1:$O$1,0))

You can try this formula in cell B2 and copy it down.

[#5]

@OliverScheurich 

The formula works for everything but Environmental for some reason. Do you have any idea why?

[#6]

Never mind! I figured it out! Thank you so much for your help!