Code for index, indirect, lookup etc

Hello,


I have a sheet with 2 columns. Column A has Make of the car and column B has the model of that make. Since past 3 days i am googling around and posting on all forums to figure out how can i:

1. Have a dropdown to select "Make" in a cell and
2. Get corresponding model (again in a dropdown) in another cell.
3. Auto complete feature would be a luxury but not a necessity. For eg, i type H and it gives me Honda and Hyundai in drop down.

Please find attached sheet. Getting D3 is easy, i can use LIST and validation. But how do i get E3? Without any helper column? Also i may add more data below in future and it should update automatically. I dont mind giving 10,000 rows as reference in advance.
PS: i can work hard and make sure that Values in column B remain UNIQUE (If that makes things easier) Is it possible to have duplicate values in column B and still have unique values in drop down box?

I tried using all possible functions and data validations and LOTs of helper columns, but i think it's not possible without vba.

Below is my another thread on same issue posted under formulas and functions.

http://www.excelforum.com/excel-form...drop-down.html

Thank you for your time.

Originally Posted by moonbreakker

3. Auto complete feature would be a luxury but not a necessity. For eg, i type H and it gives me Honda and Hyundai in drop down.

ActiveX combobox with vba solution
1) Sheet1 code module

Please Login or Register  to view this content.

2) ThisWorkbook code module

Please Login or Register  to view this content.

ComboBox1 liked with D3
ComboBox2 linked with E3

So you can refer those cells for formula or something else.

Oh! awesome! Jindon that was what i exactly needed. I will have to read codes for 2 days. :-) Got what i needed. Thank you. Thank you TMS too for your time. I did not want to create a matrix or a helper column because it's difficult to maintain it (error proof).

Thanks.

It's too much to ask for Jindon.. If you don't mind can you explain me in very brief Set dic = CreateObject("Scripting.Dictionary") and CreateObject("System.Collections.ArrayList") ?

Sorry, just over excited with the neat output.

Dictionary should be in VB help and also see

https://msdn.microsoft.com/ja-jp/library/cc428065.aspx

ArrayList see

https://msdn.microsoft.com/ja-jp/lib...v=vs.110).aspx