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?

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?

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?

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