Data Validation List

anillinda

New Member
Joined
Apr 20, 2012
Messages
48
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Is this, copied down, what you mean?

Excel Workbook
ABC
1SAM JOHNJOHNSAM JOHN
2THOMAS JOHNTHOMAS JOHN
3JOHN THOMASJOHN THOMAS
4NEIL WILSONJOHN AMY
5JOHN AMYAMY JOHN
6AMY JOHN
7AMY GARET
List matches
 
Upvote 0
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>
[TD="bgcolor: #FFFF00"][/TD]

</tbody>
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

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)
Code:
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
 
Last edited:
Upvote 0
Peter,
My data resides on worksheet "data"
I am accessing the data on worksheet "form"
Appreciate your help in changing the code.
Thanks
Neal
 
Upvote 0
Peter,
My data resides in worksheet "data"
and
I am accessing the data in worksheet titled
"Form"
Thanks
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top