auto populate from drop down

premedmedic

New Member
Joined
Feb 26, 2016
Messages
4
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,565
Office Version
  1. 365
  2. 2010
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
ABCDEFGHIJKL
1CoJob1Shirt13CoJob1CoJob2CoJob3code1code2code3
2SweaterSweaterShirt111519
3CoatLong CoatPants121620
4ShirtShort CoatCoat131721
5Shirt18
Sheet1
Cell Formulas
RangeFormula
C1C1=INDEX(J1:L5,MATCH(B1,INDEX(G1:I5,,MATCH(A1,G1:I1,0)),0),MATCH(A1,G1:I1,0))


This is B1:

1613871719724.png
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,093
Messages
5,622,655
Members
415,917
Latest member
kungsleden

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top