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

slent

New Member
Joined
Jul 17, 2010
Messages
4
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
:eek: untested :eek:

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

Code:
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

New Member
Joined
Jul 17, 2010
Messages
4
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

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Perhaps

Code:
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

New Member
Joined
Jul 17, 2010
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,750
Messages
5,638,138
Members
417,010
Latest member
jnuss03

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
Top