Dynamic suggestion drop down lists

**wesmortimer** · 10-26-2010, 12:12 PM

Hi,

I've searched a few forums, but despite many similar sounding threads I can't find a solution to the following:

I have a number of lists on worksheet 1 and a user form on worksheet 2. Worksheet 1 is hidden, so users open the spreadsheet then populate cells with a data validation drop-down list from worksheet1. Easy...... but not satisfactory! Some of the lists are quite long and the users now want to be able to start typing the list text so they can then select the correct text from the drop-down in a 'searched' or even auto-populated way.

Is this possible (I'm running Excel 2003)?

(I tried hiding rows of data above the input array so it would autopopulate, but due to variations on the same names it doesn't offer a suggestion until the majority of the text is typed - also doesn't work if something has been mistyped.)

I hope this makes sense - all help is gratefully appreciated.

Cheers,

Wes

**JBeaucaire** · 10-26-2010, 12:26 PM

If you switch completely over to Control Toolbox Combo Boxes, it has an Autocomplete capability. Data validation doesn't, really.

However, I did come up with a way to use the same drop down twice. When the cell is empty, it presents a shorter list of subchoices. Then once a choice is made, you use the same cell again and the dropdown has dynamically changed to a much smaller set to choose from.

The main listing is here and there are three different examples of ways it can be used, each with a sample workbook so you can see it in action:
Dynamic Sub DV Lists in Same Cell

**wesmortimer** · 10-27-2010, 04:58 AM

Hi Jerry,

I thought I'd leave the combo boxes in this case, but your 'Dynamic Sub DV Lists in Same Cell' is ideal. Brilliant, in fact!

Thanks very much for your help - it is very much appreciated.

Cheers muchly,

Wes