Excel Drop Down List and associated data

January 5, 2011 at 02:22:47
Specs: Macintosh
I have 2 worksheet in an Excel Workbook.

The first worksheet has 2 columns, the first indicating room type and the second indicating a numerical value associated with that room type.

For example in Worksheet 1:

Cell A1: Office; Cell B1: 9
Cell A2: Lab; Cell B2: 13
Cell A3: Store; Cell B3: 5
Cell A4: Auditorium; Cell B4: 12

In the Worksheet 2, I have created a drop down list based on Worksheet 1 Cells A1:A4.

What I want to occur in Worksheet 2, is when an item is selected from the drop down list it provides in the adjacent cell the corresponding numerical value, for example if I select Store from drop down list in the adjacent cell I want it to show the 5 (the corresponding value from Cell B3), similarly if I select Lab it will show 13 (the corresponding value from Cell B2), etc, etc

I have been searching and trying various things like offset, if, etc but can't make it work.

Assistance is greatly appreciated

See More: Excel Drop Down List and associated data

January 5, 2011 at 06:58:51
Read up on VLOOKUP in the Excel Help files.

Give it a try and if you can't figure how to make it work, come on back.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 7, 2011 at 14:11:25
Both following seem to work:

=index(sheet1!$B$1:$B$4, match(A1,sheet1!$A$1:$A$4,0))

=IF(C3<>"",VLOOKUP(C3,'Lookup Lists'!A1:B4,2,0)), where C3 is location of drop down list and 'Lookup Lists' is the worksheet with list data.

Report •
Related Solutions

Ask Question