Drop down list showing different value

[dspitler]

What I am trying to do is create a drop down list where the department is
shown in the list (Art, Style, Photography) but once the name is selected,
the department number shows up. For example, if someone chose Art, I want
the department number to show up in the cell (1000).
Art 1000
Style 1001
Photo 1002
Please help! Thanks so much.

 

[akphidelt]

If you are using a data validation list you can just use a vlookup

Vlookup(A1,CreateYourLookupArray,2,FALSE)

If it is a combo box drop down, then you have to use the cell link mixed
with offset and vlookup formulas.

 

[Debra Dalgleish]

You can use programming to do this. There's a sample file here that
fills in a product code, after a product is selected. You could adapt
the technique to your workbook:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

 

[dspitler]

This is exactly what I need, but the .zip file doesn't really explain how to
create this kind of data validation. I need two different validation change
tables, so I tried just copying and pasting cells B2:B16 to another area in
the sheet. The drop-down lists copied over, but not the data change. I'm
very new at writing code, and couldn't figure out how to change the code to
fit my needs. Can someone explain how the data validation change in the .zip
file was created in the first place?