Drop Down List Box


Posted by Sean on February 05, 2001 3:01 PM

I have a column E that is titled Category. I want to setup a drop down list box that appears whenever a person comes a cell within column E with the following values:

Consulting
Corporate Admin
Corporate Services
Creative Services
Customer Support
Development
Finance
Help Desk
Human Resources
IT
Legal & Contracts
Sales & Marketing
Training

After the person selects one of these values I want the corresponding number to appear and not the name. For example, if the person selects Consulting what would it appear in the cell would be the number 2. Here is the table:

2 Consulting
3 Corporate Admin
4 Corporate Services
1 Creative Services
5 Customer Support
6 Development
7 Finance
8 Help Desk
9 Human Resources
10 IT
11 Legal & Contracts
12 Sales & Marketing
13 Training

Any ideas and help would be appreciated.



Posted by steve on February 07, 2001 6:35 PM


How about using the match function right next to your drop down box.


place your list somewhere in the background
example
place the one you want to be number 1 in cell A101 the number 2 in A102 continue on to A113.

In the cell you want the drop down list(lets call it A1, select data and choose data validation from the menu bar. Select list in the allow menu and select the in cell drop down list check box.In the source box type in A101:A113 In the cell next to the drop down list,(lets call it B1 type in the following,
=match(A1,A101:A113,o)

Let me know how it works steve