Fetching multiple match values and display in drop down using VBA

[Muneendra]

Hi All,

I am new to VBA, Please help me on below.

I have three sheets in my Excel ( Template, Vendor ID, Vendor Location).

Template sheet have the three columns ( Vendor ID, Vendor Name, Location ).
Vendor ID sheet have two columns ( Vendor ID, Vendor Name).
Vendor Location have the two columns ( Vendor ID , Location ).

1. in Template sheet i need to display the unique Vendor ID's as searchable dropdown in Vendor id Column ( Entire column should be as searchable drop down).
2. When we select the Vendor id in Template Vendor ID column, need to to display the corresponding vendor Name in vendor Name column drop down.
3.need to display the Corresponding vendor locations in the Vendor locations drop down. ( One vendor ID may contain multiple locations ).

[#2]

@Muneendra 

No VBA needed. See the attached version.

[#3]

@Hans Vogelaar,
Can you please let me know the steps you have performed to achieve this.
It will be great help, if we achieve same without using the table format in Template Sheet. ( It may include several other columns).

Thanks,
Muneendra.

[#4 · Accepted solution]

@Muneendra 

I created a few named ranges - see Formulas >Name Manager.

I used these names in Data Validation rules for column A and C.

It is not essential that the data on the Template sheet are in table format. See the attached version.

[#5]

Hi @Hans Vogelaar,

Thanks alot for your kind help. My excel may hold more than 10K rows, will it perform faster?

[#6]

@Muneendra 

Excel should be able to handle that, but you'll have to try it yourself.