Forum Discussion
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?
=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 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!)
=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.
