Reading from drop down lists

[ya7omar]

Hi All,

I have a drop down list in my excel sheet that contains names of manufacturers. What i would like to do (using vba) is display some details of each manufacturer that will be copied from a different sheet.
So my question is, how can i read the string from the drop down list on one sheet and then copy a cell from another sheet.

Thanks for the help in advance!

[arthurbr]

Hi,

this link will put you on track

[royUK]

I think you simply need to use VLOOKUP and Data Validation, take a look at the vLOOKUP example here

http://excel-it.com/excel_functions.htm

[ya7omar]

Thanks for the reply.

The only problem with Vlookup is that i have a huge list and 3 criterias to filter from. I was thinking of using a case statement in vba, i am attaching a mock file to get a better idea of what i'm trying to do.
So when the user chooses the brand, color, and type of the car the price should be displayed in cell D1. I have hundreds of entries and so thought that using a case statement to divide it into Sedan/SUV and then further into the model would be much more practical.

Is this possible to do in vba.

Thanks

[ya7omar]

Of course i forgot to attach the file

[johnjohns]

I have concatenated the fields and done a lookup (index+match). Please see the attachement

rgds

johnjohns

[ya7omar]

Thanks Johnjohn,

It works perfectly, the only issue is that with about 600 entries it looks bad to have them all in one column, I tried changing the formula to include all the fields as you can see from the attachment but it gives me a reference error. Is there a way around this?

Thanks

[johnjohns]

If you want to keep the colors columnwise, then concatenate only the company name and vehicle type and do a 2way lookup. See the attachment. Also I have moved your list items to another sheet. To read from another sheet (data validation list) you have to name the ranges.

rgds

johnjohns

[ya7omar]

Thanks for the help works perfectly.