Dropdown List with Letters, Numbers, and Formulas

Occasional Contributor

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?

5 Replies


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






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


Screenshot 2022-06-09 150137.png


best response confirmed by YoloSpaceMuffin (Occasional Contributor)



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

profile structure section landscape detail etc..JPG



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


Screenshot 2022-06-09 180518.png


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