auto populate from drop down

[premedmedic]

I am looking to make a spread sheet that will populate item codes, based off of job number, and description.
Job 1 has selections as sweater, coat, shirt corresponding codes 11, 12, 13
Job 2 has selections as sweater, long coat, short coat, shirt corresponding codes 15,16,17,18
Job 3 has selections as shirt, pants, coat corresponding codes 19,20,21
So when user selects Job 1 from drop down choices then next item limits them to only the choices that are for that job and then it auto poluates corresponding code in next cell
User selects Job 1, then sweater it auto populates 11 in third column

Thank you
Tracey

 

[kweaver]

This is a tricky dependent drop-down problem. One issue is that Job1, etc. looks like a cell reference to Excel.
Therefore, I called them CoJob1, etc.

You can put columns G through L on another sheet if you want.

After selecting G1:I5, you need to make them name ranges using the top line for the name. (Formula -- Create from selection -- top [only])
Then, the drop-down in B1 uses an indirect reference.

If you look this over, hopefully you'll see how to do it.

Book12
	A	B	C	D	E	F	G	H	I	J	K	L
1	CoJob1	Shirt	13				CoJob1	CoJob2	CoJob3	code1	code2	code3
2							Sweater	Sweater	Shirt	11	15	19
3							Coat	Long Coat	Pants	12	16	20
4							Shirt	Short Coat	Coat	13	17	21
5								Shirt			18
Sheet1

Cell Formulas
Range	Formula
C1	C1	=INDEX(J1:L5,MATCH(B1,INDEX(G1:I5,,MATCH(A1,G1:I1,0)),0),MATCH(A1,G1:I1,0))

This is B1: