Linking a cell to a drop down list

[saintraheel]

What I would like to do is have a drop-down list that lists all the
divisions/departments of a company, and when I select a particular
division/department from the drop-down list, I would like the box
underneath or anywhere on the worksheet to automatically update with
the appropriate ID number that is assigned to each division. Is this
possible? If so, how can this be done in excel? Thanks.

 

[vezerid]

Let us say you have your department ID's in A1:A10 and department
descriptions in B1:B10.

Right-click the menu area and bring up the Forms toolbar.
Click Combo-box and draw it on the screen.
While still selected (select with Ctrl+click if necessary) double-
click and go to the Control tab.
Set Input range to B1:B10. Thus your combo box will show the
department names.
Set Cell link, say, G2. Now, once a department is selected, its
ordinal number within B1:B10 will appear in G2.
Choose another cell to display department ID. In this cell put the
formula:
=INDEX(A1:A10,G2)

The last cell will show the department ID.

HTH
Kostis Vezerides

 

[Gord Dibben]

Use Data Validation for in-cell drop-down for selecting an item and VLOOKUP
for filling in the ID number.

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP