SOLVED

# Dropdown List with Letters, Numbers, and Formulas

Occasional Contributor

# Dropdown List with Letters, Numbers, and Formulas

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

# Re: Dropdown List with Letters, Numbers, and Formulas

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

# Re: Dropdown List with Letters, Numbers, and Formulas

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

best response confirmed by YoloSpaceMuffin (Occasional Contributor)
Solution

# Re: Dropdown List with Letters, Numbers, and Formulas

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

# Re: Dropdown List with Letters, Numbers, and Formulas

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

# Re: Dropdown List with Letters, Numbers, and Formulas

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