| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
list in data validation
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
list in data validation
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 LinkedCelltype 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 -- Biff Microsoft Excel MVP "benji" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
list in data validation
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 "benji" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
list in data validation
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 On Thu, 4 Feb 2010 09:58:05 -0800, benji wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
list in data validation
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 "benji" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I get a Data validation list to select another validation l | New Users to Excel | |||
| data validation list: how do i 'force' a user to enter data from the list? | Excel Discussion (Misc queries) | |||
| data validation list: how do i 'force' a user to enter data from the list? | Excel Discussion (Misc queries) | |||
| data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
| data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Linear Mode
