Data Validation List

[anillinda]

I have a list consisting of names in column A. I would like to type the name in a cell and would like to see the names that match that criteria.

for example: the list has the following names:

SAM JOHN
THOMAS JOHN
JOHN THOMAS
NEIL WILSON
JOHN AMY
AMY JOHN
AMY GARET

IF I WERE TO TYPE IN THE CELL JOHN,, THEN ALL THE VALUES SHOULD SHOW UP THAT HAS JOHN IN THEIR NAME.
THANKS

 

[Peter_SSs]

.. worksheet titled "Form", In that worksheet, I want one cell may be a1 to bring up all the names that match to the name that is typed ...

So, it sounds like you want cell A1 to have a Data validation drop-Down list that is populated only with the names that match (that is contain) the "name that is typed"?

If that is so, we have to know what name, or part name, we are trying to match against. What I have been asking is "Where do we find that name or part name typed by the user?"


If you mean you want that to be dynamic and the original list gradually diminishes as letters are typed in that cell A1, then you can't do that with standard Data Validation but would need something like this.

 

[Yongle]

how do I change the vba code, if my employee list is in the worksheet "Data" and I am trying to access the employee information on another worksheet titled "form", where the employee can type the name of the employee and all the matched names should appear on the list, once the list shows the matched names then the employee can select the correct name.

The easiest way would be to convert the list on sheet Data into an Excel table

The code below assumes that
- names are in the first column (field 1) of the table on sheet Data
- names are in column A on sheet Form

Before_RightClick event used to trigger the macro (use a different trigger if you prefer)
- right-click on header above names in table (on sheet Data) to see input box to enter a name
- right click on any name in table and that name is selected on sheet Form

Code to go in SHEET module of sheet Data (see previous instructions)

Option Explicit
Private Const fld = [COLOR=#008080]1[/COLOR]                   'field in table on sheet "Data"
Private Const col = "[COLOR=#ff0000]A[/COLOR]"                 'column containing names in sheet "Form"


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim tbl As ListObject:  Set tbl = Me.ListObjects(1)
    If Not Intersect(tbl.ListColumns(fld).Range, Target) Is Nothing Then
        Cancel = True
        If Not Intersect(tbl.HeaderRowRange(fld), Target) Is Nothing Then
            FilterNames
        Else
            FindName (Target.Value)
        End If
    End If
End Sub

Sub FilterNames()
    Dim nStr As String:     nStr = "=*" & InputBox("Name to find?") & "*"
    With Me.ListObjects(1)
        With .Range
            .AutoFilter Field:=fld
            .AutoFilter Field:=fld, Criteria1:=nStr, Operator:=xlAnd
            .Interior.ColorIndex = xlColorIndexNone
        End With
    End With
End Sub

Sub FindName(aName As String)
    On Error Resume Next
    With Sheets("Form")
        .Activate
        .Cells(WorksheetFunction.Match(aName, .Columns(col), 0), col).Activate
        If Err.Number > 0 Then MsgBox "not found"
    End With
End Sub