Make data validation list dependent on cell value

[nicke_swe]

Hi,

I have a list of products with a code structure looking like the following;
"XXXX Name" where X is a number.

The user should be able to pick the correct product from a data validation list, but since there are more than 1000 different products I would like to make it easier for the user to find the correct one.

Therfore I'm thinking of making it possible for the user to enter for example the to first number of the productcode in a cell and in data validation cell show a list only containing the choices starting with the number entered by the user.

Cell A1=12
Cell A2 (data validation)=1200 Apple, 1211 Banana, 1212 Lemon, 1223...

Cell A1=121
Cell A2 (data validation)=1211 Banana, 1212 Lemon

What is the most efficient way of doing this?

 

[VoG]

Perhaps dependent lists http://www.contextures.com/xlDataVal02.html

 

[nicke_swe]

Thank you for the quick reply, but to my understanding that will not work since I want the user to be able to enter one, two, three or even four numbers of the code, and hence drill deeper into the structure and limit the choices even further.

 

[VoG]

What about a combobox? http://www.contextures.com/xlDataVal14.html

 

[nicke_swe]

I'm trying to stay away from Macros if possible due to the security warnings which are making users nervous.