Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Drop down lists
Is it possible to program a drop down list to change according to the search
criteria. For example if €œa€ is entered into the cell, the drop down list displays all value beginning with €œa€ in the drop down table. If €œac€ is entered only values that start with €œAC€ are displayed ect.. ect€¦ Is there a marco that can do this? |
#2
|
|||
|
|||
considering that you add a combobox from the control toolbox, and your range
is A1:A10, and you want to enter the lookup value in cell B1, then use the following code: Private Sub Worksheet_Change(ByVal Target As Range) Set rng = Range("A1:A10") If Target.Address = "$B$1" Then If ComboBox1.ListCount 0 Then For j = 0 To ComboBox1.ListCount - 1 ComboBox1.RemoveItem 0 Next j End If If IsEmpty(Target.Value) Then For i = 1 To rng.Rows.Count ComboBox1.AddItem rng(i) Next i Else For i = 1 To rng.Rows.Count If Left(rng(i), Len(Target.Value)) = Target.Value Then ComboBox1.AddItem rng(i) End If Next i End If End If End Sub Mangesh "Chris" wrote in message ... Is it possible to program a drop down list to change according to the search criteria. For example if "a" is entered into the cell, the drop down list displays all value beginning with "a" in the drop down table. If "ac" is entered only values that start with "AC" are displayed ect.. ect. Is there a marco that can do this? |
#3
|
|||
|
|||
Autocomplete? Think this previous post by Debra Dalgleish
would be of interest: "Although data validation doesn't support autocomplete, there's a sample file here that provides a combobox from which you can select one of the values from the data validation list. In the combobox, you can enable autocomplete: http://www.contextures.com/excelfiles.html Under 'Data Validation', look for: Data Validation Combobox Named Lists " -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Chris" wrote in message ... Is it possible to program a drop down list to change according to the search criteria. For example if "a" is entered into the cell, the drop down list displays all value beginning with "a" in the drop down table. If "ac" is entered only values that start with "AC" are displayed ect.. ect. Is there a marco that can do this? |
#4
|
|||
|
|||
Chris,
Dynamic Data Validation of type list would do this. Given a list with sorted search terms called Terms and a cell F4 with data to use Use the following formula as your list validation: =OFFSET(Term,IF(ISNA(MATCH(F4,Term,1)),1,MATCH(F4, Term,1)+IF(ISNA(MATCH(F4,Term,0)),1,0))-1,,IF(OR(ISNA(MATCH(F4&"z",Term,1)),F4=""),ROWS(Te rm),MATCH(F4&"z",Term,1))-IF(ISNA(MATCH(F4,Term,1)),1,MATCH(F4,Term,1)+IF(IS NA(MATCH(F4,Term,0)),1,0))+1) Note that you can even create your validation list *on* F4, but then you need to disable the error warning. Looks almost professional:) Dm Unseen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Symbols in Drop Down Lists | Excel Worksheet Functions | |||
Data Validation - Drop down lists - if then? | Excel Discussion (Misc queries) | |||
multiple drop down lists | Excel Discussion (Misc queries) | |||
Can different drop down lists be displayed depending on the value. | Excel Discussion (Misc queries) | |||
How do I change the appearance of my drop down lists? | Excel Worksheet Functions |