SOLVED

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

@YoloSpaceMuffin 

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

 

jitinm_0-1654778009545.png

 

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

 

Screenshot 2022-06-09 150137.png

 

best response confirmed by YoloSpaceMuffin (Occasional Contributor)
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.

profile structure section landscape detail etc..JPG

@Quadruple_Pawn 

 

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!