Combo Boxes in Excel

[IronHand]

Good Day,

I have an excel spreadsheet that I need to use combo boxes. I know that Access would be so much nicer, but the user wants it in Excel.

I was wondering how to setup this spreadsheet. I have lookup data on one sheet.

I want to be able to Choose the Employee name and have the site that is beside their name automatically fill in too. I will also be making a combo box for the Function as well.

EG:
Site Employee Function
Calgary John Project Manager
Sask Jo Team Lead

Do you have to put a combo box for each line? I just can't figure out how to get this to work properly.

Any suggestions would be great.

Thank you,
Jennifer

 

[shades]

Howdy, Jennifer. I think it better to not use combo boxes.

I approached it using two techniques that are relatively simple, but allow expansion automatically.

1. Dynamic Defined Named Ranges
(I changed the worksheet name to Data). Then I put three dynamic named ranges for this worksheet:

Name: MyTable

Refers to:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),3)

Name: EmployeeName

Refers to:

=OFFSET(Data!$B$1,0,0,COUNTA(Data!$B:$B),1)

Name: MyTableHead

Refers to:

=OFFSET(Data!$A$1,0,0,1,COUNTA(Data!$1:$1))


Now, even if you add names to your list, it will automatically expand. And if you add other headings (like Function in your table) the new heading and data below will pick it up.

-----------------------

2. Data Validation:

Then on the worksheet ("Test"), I selected cell D2, and used Data validation (Data > Validation), and used List, and put in the following:

=EmployeeName



3. Formulas

In Cell C2, I put this formula:

=INDEX(MyTable, MATCH(D2,Test.xls!EmployeeName,), MATCH("Site",MyTableHead,))

Since you are using dynamic named ranges, the formula automatically adjusts. I filled the columns C and D down a few rows to show you the results.

Hope this helps.
________
Vermont marijuana dispensaries