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]

Is this, copied down, what you mean?

Excel Workbook
	A	B	C
1	SAM JOHN	JOHN	SAM JOHN
2	THOMAS JOHN		THOMAS JOHN
3	JOHN THOMAS		JOHN THOMAS
4	NEIL WILSON		JOHN AMY
5	JOHN AMY		AMY JOHN
6	AMY JOHN
7	AMY GARET
List matches

 

[Yongle]

Here is a VBA solution
- after following instructions below, type a value into A1 and enter
- uses Excel Table with filter to give you the list of names

Leave A1 blank and copy list of names as below with Names header in cell A2 etc
Excel 2016 (Windows) 32 bit

Names

THOMAS JOHN

JOHN THOMAS

NEIL WILSON

JOHN AMY

AMY JOHN

AMY GARET

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Insert VBA below in sheet module
(right-click on sheet tab \ View Code \ paste code into code window \ {alt}{F11} to go back to Excel)

Option Explicit


Sub FilterList()
    Dim rng As Range, A1 As String
    Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    A1 = "=*" & Range("A1").Value & "*"
    
    On Error Resume Next
        Range("A2").ListObject.Unlist
    On Error GoTo 0
    
    With Me.ListObjects.Add(xlSrcRange, rng, , xlYes)
            .TableStyle = ""
        With .Range
            .Font.Color = vbBlack
            .AutoFilter Field:=1
            .AutoFilter Field:=1, Criteria1:=A1, Operator:=xlAnd
            .Interior.ColorIndex = xlColorIndexNone
        End With
    End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        FilterList
        Target.Activate
    End If
End Sub

 

[anillinda]

Peter,
My data resides on worksheet "data"
I am accessing the data on worksheet "form"
Appreciate your help in changing the code.
Thanks
Neal

 

[anillinda]

Peter,
My data resides in worksheet "data"
and
I am accessing the data in worksheet titled
"Form"
Thanks

 

[Peter_SSs]

My data resides in worksheet "data"

Where on that sheet?

I am accessing the data in worksheet titled
"Form"

Where on that sheet is the first formula to be?

I would like to type the name in a cell ..

Which sheet and what cell would you be doing this?

 

[anillinda]

Where on that sheet?
Data is on worksheet titled "Data" in column A from row 3 to row 350

Where on that sheet is the first formula to be? the worksheet name is "Form" and the data will be entered in cell a1

Which sheet and what cell would you be doing this?

Worksheet is Form and the cell where the options should populate is a1 as a drop down list, after the name match is found.

 

[Peter_SSs]

Worksheet is Form and the cell where the options should populate is a1 as a drop down list, after the name match is found.

I don't know which one(s) of my 4 questions that is attempting to answer.

I also don't know what the blue means.

Can you please re-address the previous questions and identify what answer(s) apply to each?

 

[anillinda]

Sorry for the confusion.
First I have two worksheets and the first worksheet is titled "data", which contains employee data, in that worksheet column "A" has all the names beginning in cell "a3" and down. My goal is to give this worksheet to my staff, such that they can only access one employee information at a time. To do that I have created another 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 and then the staff can choose the correct name from the matched names. Once the name is selected then I can populate rest of the data using index and match formula.
Appreciate help!
Neal

 

[anillinda]

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.
Thanks
Neal