Drop down list quicker selection

[Nick]

We are using Excel 2003
We created a drop down list using the Data\Validation process, which works
fine.

When the arrow within a cell is clicked on to show the drop down list, how
can you quickly select one of the many names in the list just by typing the
first character of a name within the list.
At the moment, we have to scroll up and down the list to select a particular
name, which in itself isn't a problem, but slows down the process of finding
the right name.

Can the names within the list be alphabetically grouped when inputting the
first character from the keyboard, and speed up the selection.

I hope this makes sense!
Any help would be appreciated.

Thanks

Nick

 

[Max]

When the arrow within a cell is clicked on to show the drop down list, how

can you quickly select one of the many names in the list just by typing
the
first character of a name within the list.

Debra Dalgleish shows a way to "build in" the AutoComplete feature into DVs,
which finds matching items in the list as you start to type ..

http://www.contextures.com/xlDataVal10.html
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100, Files:360, Subscribers:56
xdemechanik
---

 

[Roger Govier]

Hi Nick

In order to use Autocomplete, you need to use a Combo Box.
With regard to listing the entries from the first character, this can be
done with Data Validation with the aid of a macro.
I have a sample workbook on Debra Dalgleish's site
http://www.contextures.com/DataValHeadings.zip

 

[Nick]

Hi Roger

Thanks very much for your swift response!

I'm still unsure though, how to go about setting the macro up from the
example.
Is there a link to a tutorial for this?

Thanks again

Nick

 

[Roger Govier]

Hi Nick

With my example file open, and the file in which you wish to use the macros,
Press Alt+F11 to invoke the Visual Basic Editor.
In the Project Explorer (press Control+R if it is not visible),
double click on VBAProject DataValHeadings.xls
This will show a list of the Sheets in the Workbook, and Modules.
Double click the Modules, and you will see Module1
Inside there, you will see 3 different macros which are used to Remove all
letters, Sort and Add all letters of the alphabet, and Sort and Add only
letters used in your List.

The easiest way to copy these to your workbook, is to click on Module1, hold
down the left mouse key as you drag the module and just drop it on your
workbook. This will copy the Module to your workbook.
Either ensure that teh sheet in your Workbook which contains your list of
items is named List, or change the name in the macros from
Worksheets("List").Activate
to whatever the name of your sheet is.

To use just type Alt+F8 to bring up the macro list>highlight the relevant
macro>Run

 

[Nick]

Many thanks Roger for your concise description. It worked well!

Regards

Nick