Excel 2007 : Vlookup returns multiple values in Dropdown

Hello All,

I have a sheet where I have names of cities in B2 (in a dropdown) & in sheet 2 I have all equipments in those cities. What I want to do is when I select City from Sheet1 (B2) it should give me corresponding values from Sheet 2 in Sheet 1 B2 in a dropdown box. I use vlookup but it gives me first value. What I want is all values corresponding to the city in dropdown
Sheet1 looks like this
A B
1 City (Names of all Cities in dropdown)
2 Equipment (should give me corresponding values from sheet 2 in dropdown)

Sheet2 looks like this
A B
1 New York Drive
2 Alaska RAM
3 New York CPU
4 New Jersey Monitor
5 New Jersey Floppy drive.

Try this in the worksheet module for Sheet1

Please Login or Register  to view this content.

I have also made a few other changes to your original sample (demo file attached to this post)

Validation of other cells are now from Sheet2 and called using named range "State" this list is dynamic so you can add or subtract from it as you wish

You should consider doing the same with your list of equipment.

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.


Tips:-
1/. Add a location to your profile, it helps with prioritising replies according to time zones and any possible language interpretation problems.

2/. Add the version of Excel you are using there are significant differences between them and it helps to know when answering problems.

Hi Marcol,

For some reason the code has stopped working again like in the example if I select the state then it does not gives me the component. Also at times when I select some state it goes to the lookup sheet(does not happens everytime). The Worksheet_Change event does not fires at all.

This is my original code

Please Login or Register  to view this content.

Thanks,

Amit

Please wrap your code in code tags, before the moderators get you...

Forum rules

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

Do this first then post your workbook or sample so it can be checked.
The code I gave you seems to work , so it must be your addapted caoe that has the problem.


Cheers