How do I create drop-down list boxes from multiple lists/headers

[Guest]

Sample data:

HEADERS --> Ford Chevy Jaguar Mazda Saab

radio 40 60 100 50 80
cd 80 86 110 65 87
tv 45 55 0 62 91
dvd 130 110 230 98 83

Example (drop down box on another sheet in same workbook) where you select
either Ford, Chevy, Jaguar, Mazda, Saab from the the list box and the
appropraite cells are populated with the correct pricing for radio, cd, tv,
dvd.

Thank you in advance.


Marty

 

[Biff]

Hi!

Assume these headers are on Sheet1 in the range B1:F1.

Ford Chevy Jaguar Mazda Saab

Select that range B1:F1
Goto the menu Insert>Name>Define
Enter a name for that range. Something like: Vehicles
Refers to: =Sheet1!$B$1:$F$1
Click ADD

Assume this portion of the table is in the range A2:F5.

radio 40 60 100 50 80
cd 80 86 110 65 87
tv 45 55 0 62 91
dvd 130 110 230 98 83

Enter a name for that range. Something like: Table
Refers to Sheet1!$A$2:$F$5
OK out

Navigate to Sheet2 and select the cell where you want the drop down list.
Goto the menu Data>Validation
Allow: List
Source: =Vehicles
OK

Assume cell A1 on Sheet2 is where the Vehicle drop down list is. Cell B1 is
a device type, say, CD:

Sheet2A1 = Chevy
Sheet2B1 = CD

To get the price in cell Sheet2C1:

=VLOOKUP(B1,Table,MATCH(A1,Vehicles,0)+1,0)

If you want, you can also create a drop down list of the device types. Just
follow the same steps as above.

Biff