dropdown + vlookup?

**lordbeta** · 01-12-2010, 10:17 AM

hello,

Im having a problem which I can't solve for quite a while now. I've a excel file consisting of 2 relevant sheets. the first one is called "extracting the list". Here i've extracted all the list into vertical list. (removing building year etc). If you scroll down you'll see all the stages of extracting which I special paste (value) pasted horizontally on top.

The second sheet is called "main". Here I've made a dropbox using data validation, as source I choose =auto, when you select all the car brands in the previous sheet you'll see I named that selection auto. The dropdown is working great, that souldn't be the problem.

The goal is that when you select a car brand from the dropdown there will appear another dropdown box under it. This one should contain all the types (can be found in the previous sheet). When the second box appeared you should select a type as well. Then it should display some specifications about the car, and if possible a picture.

The specifications can be found in the specs sheet. I doesn't matter that they are correct or incorrect. Its the aim to make this possible in excel.

Sorry if my sheets look horrible to you guys but I tried so many things and I just couldn't find the right solution. Hope one of you could help me.

Link : http://www.lordbeta-webdesign.com/excel.xls (uploading doesn't work

)

Thanks in advance,

Laurens

**teylyn** · 01-12-2010, 04:28 PM

Hi, not sure why uploading the file won't work. Maybe you can try again. Sounds like you want to do dependent data validation. Check out this link

http://www.contextures.com/xlDataVal02.html

**lordbeta** · 01-13-2010, 11:25 AM

hey!
Thanks for the response, I managed to create a dependend list now. But now the next problem occurs. I want to show the right specificantion belonging to the car.

If someone chooses Audi and then S3, excel has to show them the right specs.
has anyone a idea? tried it with vlookup + if and hlookup but didn't work out

Thanks

**NBVC** · 01-13-2010, 12:08 PM

If you are consist between sheets for each automaker (i.e. Headers always in Row 2 and specs beginning in row 4), then you can use:

=INDEX(INDIRECT("'"&$E$3&"'!2:7"),ROW($A4)-ROW($A$2)+1,MATCH($E$4,INDIRECT("'"&$E$3&"'!2:2"),0))

copied down 4 rows.

**lordbeta** · 01-13-2010, 12:32 PM

thanks for the quick reply!

you sure it will work? the formula is not refering to any other sheet? (maybe just my lack of knowledge about excel).

I tried to implement it but without succes, i've not really a clue what belongs where neither do I fully understand the functions used (index, indirect --> google gave me answers but not what I was looking for.

Could you tell me where i've to put the formula? if I put it where I think it should be its refering to empty cells ?

Tnx

**NBVC** · 01-13-2010, 12:40 PM

See attached..

The formula gets the sheetname from cell E3 so that it goes to the right sheet...

You just need to change the BMW sheet and all other sheets to ensure the headers are in Row 2, like I did in the attached sample...

**lordbeta** · 01-13-2010, 01:30 PM

Thank you so much. Guess I would never have figured this out by my self.
Also the way it works is very elegant. Especially the part that it takes the right
sheet according to the chosen brand.

I really appreciate your help, thanks!