list in data validation

B

benji

in a cell I have Data validation with validation criteria to Allow List.
Now my list is a long list of 3 alphabet codes such as
ACE
AET
AMN
ARC
ARM
ASL
ATG
ATS
AUG
AWI
BBC
BEL
BRG
CBS
CDA
CEC
CHT
COP
CPT
CSM
CWT
CWT
DBA
DTK
when I do a normal filter I can go from one code starting with say letter
"A" to another code starting with "D" by simply pressing "D" on the
keyboard. This does not happen in this list. Is there some way I can make it
happen.
 
T

T. Valko

A couple of ways...1 is a kludge, 1 uses a different type of drop down
called a combo box.

The kludge...

Add the letters to your existing *sorted* list like this:

A
ACE
ADD
B
BAD
BIG
C
CAT
CUT

Update the source range for the list.

Then, in the cell that contains the data validation list type the letter of
interest *but do not hit Enter*. Instead, click the drop arrow and the list
will scroll to the entered letter.

Another method...

Use a combo box from the Control ToolBox toolbar. This is different from a
data validation list in that the combo box drop arrow is *always* visible. A
combo box doesn't occupy a cell, it "floats" on top of the worksheet so you
have to "draw" the shape of the combo box. The combo box has an autocomplete
option that let's you do what you want, type a letter and then the
selections that start with that letter will appear. Also, if you need to use
the selection in formulas you have to use a cell that's linked to the combo
box to hold the actual selection.

Setup:

Right click any toolbar
Select: Control ToolBox
Click on the Combo Box icon (hover your mouse to see which icon is which)
Navigate to where you want the combo box to appear and left click
"Draw" the combo box
Right click the combo box
Select Properties
To set a linked cell...
Select LinkedCell>type in a cell address like A1
Select ListFillRange (this is the location of the list source)>type in a
range like F1:F10
Select MatchEntry (this is the autocomplete option)>Select 0 -
fmMatchEntryFirstLetter

Close the Properties window
On the Control ToolBox toolbar, click the Design Mode icon (the top left
icon that looks like a blueish triangle)
Close the Control ToolBox toolbar
 
L

L. Howard Kittle

Try this,

At the beginning of the A codes enter a single A, do the same for the B
codes and all the way to Z.

Now go to the cell with the data valadation in it and type in the desired
letter, say it is R. DO NOT hit enter, leave the cell in the edit mode and
click the down arrow. Will take you to the top of the R codes. Select the
code number you want.

HTH
Regards,
Howard
 
G

Gord Dibben

Couple ways to do this.

1. Howard Kittle method.

At each change of first letter in each code enter the character for the next
first letter. Include whole range in List.

A
ACE
AET
AMN
ARC
B
BBC
BEL
BRG
C
CBS
CDA
CEC

You get the picture.............

Now in your dropdown type C then hit the arrow. The list will open at C

2. Use a ComboBox with the DV dropdown.

Debra Dalgleish shows you how at her site. Involves some VBA.

http://www.contextures.on.ca/xlDataVal10.html


Gord Dibben MS Excel MVP
 
L

L. Howard Kittle

Just to clear up the DO NOT hit enter...

Sorta sounds like something really bad will happen if you do hit enter. Not
so, if you do hit enter you will have to reselect the data validation cell
to click on the arrow. Just saves you from having to re-select the cell.
Works both ways.

HTH
Regards,
Howard
 
Top