Copy data from one sheet to another based on a condition

[GuessMyPassword]

Hello all,

I'm fairly new to Excel as a whole but am looking to learn as many tricks and tips as possible so I thought I'd come to the appropriate place to start asking questions and learning! Thank you in advance for any responses and please let me know if I can provide any additional clarification.

I have a master data sheet that has 14 columns and 82 rows, with a number as a unique identifier for the data in column A. On a second tab (Sheet 2) I want to be able to type in one of the unique identifying numbers from Sheet 1, Column A, and auto-populate columns B through N in Sheet 2 with the corresponding information that is associated to the row of the unique identifying number in Sheet 1.

For instance: (Sheet 1)
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Unique Identifier #
[/TD]
[TD]Cost
[/TD]
[TD]City
[/TD]
[TD]Priority? Y o N
[/TD]
[TD]Comments
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]$100
[/TD]
[TD]New York
[/TD]
[TD]Y
[/TD]
[TD]Ship today
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]$50
[/TD]
[TD]Chicago
[/TD]
[TD]Y
[/TD]
[TD]Ship by end of week
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]$21
[/TD]
[TD]Dallas
[/TD]
[TD]N
[/TD]
[TD]After weekend
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]$39
[/TD]
[TD]Los Angeles
[/TD]
[TD]Y
[/TD]
[TD]Ship on 11/14/2017
[/TD]
[/TR]
</tbody>[/TABLE]


Now on Sheet 2, I would input "4" in column A and the following columns would copy over the corresponding information from Sheet 1.
[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]Unique Identifier #
[/TD]
[TD]Cost
[/TD]
[TD]City
[/TD]
[TD]Priority? Y o N
[/TD]
[TD]Comments
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]$50
[/TD]
[TD]Chicago
[/TD]
[TD]Y
[/TD]
[TD]Ship by end of week
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is there a formula that I can put in Column B --> on in Sheet 2 to call and copy that information once I type in a number in Column A on Sheet 2?

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet2 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter an ID in column A and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundVal As Range
    Set foundVal = Sheets("Sheet1").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        Sheets("Sheet1").Range("B" & foundVal.Row & ":N" & foundVal.Row).Copy Target.Offset(0, 1)
    End If
    Application.ScreenUpdating = True
End Sub

 

[LloydFinancials]

Here is a formula you can enter on Sheet2 in cell B2 given the name of your other sheet is "Master Data" and your data is in A1:E5. Copy over and down:
=INDEX('Master Data'!$A$1:$E$5,MATCH($A2,'Master Data'!$A$1:$A$5,0),MATCH(Sheet2!B$1,'Master Data'!$A$1:$E$1,0))

 

[GuessMyPassword]

Thank you for the quick response! I keep seeing a #N/A error saying that the value is not available when I use that formula, but I really like the idea of using an Index rather than working in VB in excel (although the first solution posted does work, thank you @mumps !). Since my data is in cells A2:N82 (with A1:N1 being column headers) would that cause the formula to return that error? My master data tab is named Master Data so I know it is referencing the correct sheet, but it isn't returning any values.

Any thoughts?

 

[mumps]

Glad to help.

 

[LloydFinancials]

I am guessing you modified the formula to fit your needs. What is that formula?