automatically fill from dynamic list as I type

**2funny4words** · 06-10-2009, 12:01 AM

I am not sure of what is it called, but I would like to be able to start typing in a cell and it to start giving me options until there is only one.

Right now I have 9 dynamic lists with anywhere from 5-20 names per list and I have a report form with multiple data validated drop down lists each pulling from a separate dynamic list. This works ok, but as the user tabs to a cell with the drop down, they must use the mouse to initiate the drop down and then select the right data. Even if the user types the right data into the cell, it will not pass validation. Is there a way to start typing and it to keep throwing away things until only one option is available and then be able to use the tab button to enter that data into the cell and move to the next cell? Or as another option how hard would it be to activate the drop down when the cell is selected and then use the arrow keys to select the right one and tab to the next cell? I would want to be able to tab through the cell if no information was needed from that dynamic list.

thanks

**Paul** · 06-10-2009, 12:11 AM

Hi 2funny,

When you land on a cell with data validation, you can press ALT + DOWN ARROW to open the drop-down list. You can then use the arrow keys to move up/down the list and Enter to select the currently highlighted option.

**2funny4words** · 06-10-2009, 07:57 PM

thanks paul, I did not know about being able to do that. Is there a way to start typing the word and it dropdown with the viable options?

**Paul** · 06-10-2009, 10:44 PM

You can't do that with a data validation drop-down list, but you should be able to using a combo box (ActiveX controls in Excel 2007, Toolbars -> Controls Toolbox in Excel 2003).

**2funny4words** · 06-10-2009, 10:51 PM

I had considered doing this, but thought that I could not use a dynamic list using a combobox?

**Paul** · 06-10-2009, 11:05 PM

You can create a dynamic list source for your combobox.

Let's say you have a list of names in A1:A10. Create a defined name called "MyNames" (or any valid name) and set the formula to:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

In Design Mode, right-click on your combobox and choose Properties. Set the List Source property to: MyNames (or the name you gave it, of course). Take it out of design mode and all of your values in A1:A10 should be available in the combobox. If you add names to A11:A15, those names will appear the next time you use the combobox.

Hope that helps!

**2funny4words** · 06-10-2009, 11:51 PM

thanks paul,

I already had the dynamic lists created, but didn't know how to utilize it with comboboxes. Do you have a good source for learning how to work with comboboxes? I will do a little researching and then post a new thread if I can not answer some of my immediate questions as far as the easiest way to change over to comboboxes from merged cells with data validation, including but not limited to: how to use tab to flow smoothly between cells and comboboxes, how to insert comboboxes into code instead of using range("??"), and how to validate the input to only what is on the dynamic list.

Thanks again, I will mark this thread solved but will stay subscribed if anyone has comments or knows a good place to go to learn.