Go to a cell in column, based on a reference number

Hello All,
What I have is a list of unique file numbers in colmun A and information pertaining to each file in that column,(file name, dates, etc). What I would like to do, is to create a formula where I could enter a reference number into a blank cell, a number that matches one of the numbers in cloumn A, and be taken not only to that column, but a predetermined row next to that column so that I may enter more information.


EXAMPLE: I want to be able to enter say the first number "0732556005" into a cell and automatically be taken to C3 so that I can fill in the blanks.
.......A............................B.....................C
0732556005..............MARTINEZ
0806798004..............HILL
0733453653..............NIETO
0727552104..............PHILLIPPI
0724752147..............STATEN
0827555212..............LAMBSON
0733331605..............JUANGCO
095395JAP..............WARD
0801131590..............TREJO
0705153068..............FOX
096637MLR..............BANSEMER
0618631063..............PETERSON
0617798002..............GANDY
098297MZ..............DACOSTA
0707344543..............HINSEY
097681BKS..............HUCKABY
0733756111..............WELLS
0636034503..............ALVERIO
0728331038..............HOWARD


Please let me know if this is enough information. Thanks guys!

Hi Joshua, the following code may work for you. It would go in the worksheet module for the sheet containing your list. Adjust the ranges to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Target.Cells.Count > 1 Or Target.Value = "" Then Exit Sub
If Not Intersect(Target, Range("A22")) Is Nothing Then
    Set r = Range("A1:A19").Find(Target.Value, Range("A1"), xlValues, xlWhole, _
        xlByRows, xlNext)
    If r Is Nothing Then
        MsgBox "Value entered not found in list."
    Else
        r.End(xlToRight).Offset(0, 1).Select
    End If
End If
End Sub

This code checks to see if you entered data into cell A22. (Change to another cell reference if you prefer.) If so, it searches A1:A19 for that value and if found will select the next open cell to the right. If not found, you get a message saying so. Hope that helps.

Hi Joshua@work,

Have a look at the two solutions (one via a filter the other by a macro) on the attached workbook.

Kind regards,

Robert

Hi Joshua,

Try this event macro:

Private Sub Worksheet_Change(ByVal Target As Range)

    '//Macro written by Trebor76//

    If Target.Address = "$A$1" Then
    
        'If the cell has been cleared, then...
        If Len(Range("A1")) = 0 Then
            '...do nothing.
            Exit Sub
        End If
    
        Dim rngFoundCell As Range, _
            rngMySearchRange As Range
        Dim varMySearchValue As Variant
               
        varMySearchValue = Range("A1").Value
        
        Set rngMySearchRange = Range("A4:A" & Range("A" & Rows.Count).End(xlUp).Row)
    
        Set rngFoundCell = rngMySearchRange.Find(What:=varMySearchValue, LookIn:=xlFormulas, _
                                                       LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                       MatchCase:=True, SearchFormat:=False)
        
        'If the 'rngFoundCell' variable has been set, then...
        If Not rngFoundCell Is Nothing Then
            '...put a 'x' in the 'rngFoundCell' row of Column E.
            Range("E" & rngFoundCell.Row).Value = "x"
            Range("A1").Select
        'Else...
        Else
            '...inform the user that no match was found.
            MsgBox "There was no match for " & """" & varMySearchValue & """" & " in sheet """ & ActiveSheet.Name & """ for the set range of " & rngMySearchRange.Address & ".", vbExclamation, "My Search Editor"
        End If
    
    End If
    
        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("E4:E3000"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 1).ClearContents
                Else
                    With .Offset(0, 1)
                        .NumberFormat = "dd mmm yyyy hh:mm:ss"
                        .Value = Now
                    End With
                End If
                Application.EnableEvents = True
            End If
        End With
        
End Sub

Regards,

Robert