Type in a # and have it pull info from another worksheet?

[slent]

Newbie here and needing help. I have a workbook with multiple sheets. First sheet has my product list with prices (name, front, mail in, P&H, total). 2nd sheet has my receipt # and the next column is a dropdown for the names (sheet 1) and I used vlookup to populate the front, mail in, P&H, total columns. What I want to do is create a 3rd sheet so when I type in the receipt # (will match a # from sheet 2) it will populate the info from the row that matches that receipt #. I am lost and looking for any help I can get. Thank you in advance I will be so appreciative.

 

[jbeaucaire]

The function you need to read up on is VLOOKUP().

 

[VoG]

untested

Right click Sheet3's tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range
If Target.Column = 1 Then
Set Found = Sheets("Sheet2").Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then
    Application.EnableEvents = False
    Found.EntireRow.Copy destinaton:=Target
    Application.EnableEvents = True
End If
End Sub

 

[slent]

Thank you for the quick responses. I copied & pasted into the view codes but when testing I type in a # that is in sheet 1 column 1 and hit the tab key it throws me into the code and highlights the word "destination". And I don't need the entire row just 4 columns to populate in sheet 3. How would I change the code for that? Thank you for any and all help.

 

[VoG]

Perhaps

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range
If Target.Column = 1 Then
Set Found = Sheets("Sheet2").Columns("A").Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then
    Application.EnableEvents = False
    Found.Offset(, 1).Resize(, 3).Copy Destination:=Target.Offset(, 1)
    Application.EnableEvents = True
End If
End Sub

but a set of VLOOKUPs should do the trick also.

 

[slent]

I don't know if vlookup would work because I want to be able to type the receipt # on sheet 3 (there will be over 1,000 #'s) and then have it find that # on sheet 2 and pull in the data from just a few columns in that row. Is this possible? Sorry about what's probably something easy for someone on this site but it's way over my head.

 

[VoG]

Here's an example of using VLOOKUP to pull data from a different sheet http://www.contextures.com/xlFunctions02.html

 

[slent]

Thank you. I think I understand enough to make that work.