Need to Recall the data from another Sheet without using VLOOKUP or User Form

[anuwars]

Dear Friends,

I am new and first time into this forum. I am working on a small work book for our office. Kindly help me to complete the same.

In sheet1 - I have selected some cells to key in data ( eg. A4, B6,B9, C10,C14). This data has been copied to Sheet2 in a row with correct serial as ( A2,B2,C2,D2,E2,).

Note: A1,B1,C1,D1,E1 is carrying the title of the row.
Sheet1 data is copied to Sheet2 with the help of a macro vb code.
Down rows of Sheet2 also copied whenever the records entered in sheet1
.
Now what I need is,
In the sheet1 - On the cell A1, if i write the lookup values for sheet2 rows, i want to display back all those cells which is used initially.
Note: I don't want to use the vlookup formula
I don't want to create a user form in the excel

Simply when i write A1 and enter(or) using any button, my related data to be displayed back in A4,B6,B9,C10,C14)

Hope i have clarified my requirement. I need help from you my friends. Hope you will help me.

 

[Fluff]

Hello & welcome
Is this what your after?

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim Rw As Long

    If Not Target.Address = "$A$1" Then Exit Sub

Application.EnableEvents = False

    Rw = WorksheetFunction.Match(Range("A1").Value, Sheets("Sheet2").Range("A:A"), 0)
    With Sheets("Sheet2")
        Range("A4") = .Range("A" & Rw)
        Range("B6") = .Range("B" & Rw)
        Range("B9") = .Range("C" & Rw)
        Range("C10") = .Range("D" & Rw)
        Range("C14") = .Range("E" & Rw)
    End With

Application.EnableEvents = True

End Sub

This needs to go in the sheet module.
Right Click on the sheet tab in Excell, select View Code & paste the above code in the window

 

[anuwars]

Thanks a lot Fluff. Super. It was perfectly working, But i have a problem while applying this code.
.
When i write the correct record in A1 - Data displayed in respective cells.
When I write the wrong record in A1 - It is going out of the excel and showing error
.
Agan When i try with correct record in A1 - It is not working.
.
Please show me an alert stating " No Records in Sheet2" incase of wrong record in A1 instead getting out in excel
.
thanks in advance

 

[Fluff]

Give this a go

Private Sub Worksheet_Change(ByVal Target As Range)
'anuwars
    Dim Rw As Long

    If Not Target.Address = "$A$1" Then Exit Sub

Application.EnableEvents = False

    On Error Resume Next
    Rw = WorksheetFunction.Match(Range("A1").Value, Sheets("Sheet2").Range("A:A"), 0)
    On Error GoTo 0
    If Rw = 0 Then GoTo xit

    With Sheets("Sheet2")
        Range("A4") = .Range("A" & Rw)
        Range("B6") = .Range("B" & Rw)
        Range("B9") = .Range("C" & Rw)
        Range("C10") = .Range("D" & Rw)
        Range("C14") = .Range("E" & Rw)
    End With

Application.EnableEvents = True

Exit Sub

xit:
    MsgBox "No Records in Sheet 2"
Application.EnableEvents = True

End Sub

 

[anuwars]

Thank you my friend. I will test and let u know.

 

[anuwars]

Dear Fluff

Not working.. Please guide further

 

[Fluff]

Not working.. Please guide further

In what way is not working? Any error messages? If so what & where?

 

[anuwars]

Dear Fluff.

I am sorry. It is working fine now. If any problem, i will let you know.
Thanks for your guidance.

Thanks

 

[anuwars]

Dear fluff, when I am trying the code in 2007 excel. It is working fine. But not working EXCEL 2013. Please help as our office changed TO MSOFFICE 2013. Expecting your reply.