Return Related Information From Another Workbook

[tammeegoreng]

Hi All,

I'm a beginner at Excel and it's my 2nd time to this forum.

I want to write a Macro to solve my problem.

Basically we have an Invoice workbook "SampleInvoice.xls" with a drop-down list to select from a list of products, listed within the Data workbook "SampleData.xls" (which includes descriptions, codes, prices related to that product).

So far, when you select the product, the macro will locate the product inside the Data Workbook, and return the Description of the product to the same cell in the drop-down cell.

My question is:
1) The cell to the left should bring up the Code for that product automatically
2) The cell to the right should display the Price. But there's a catch - there is a Distributor, Trade and Retail price. Ideally that cell will be a drop-down menu which gives the 3 pricing options. When you select the suitable field, the macro will determine the Code (as identified in point 1) and match the suitable pricing accordingly.

Here is the code so far:

 
Option Explicit 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    On  Error Goto errHandler 
    If Target.Cells.Count > 1 Then Goto exitHandler 
    If Target.Column = 2 Then 
        If Target.Value = "" Then Goto exitHandler 
        Application.EnableEvents = False 
        Target.Value = Workbooks("SampleData.xls").Worksheets("Stock List").Range("C1") _ 
        .Offset(Application.WorksheetFunction _ 
        .Match(Target.Value, Workbooks("SampleData.xls").Worksheets("Stock List").Range("B1:B500"), 0), 0) 
    End If 
     
exitHandler: 
    Application.EnableEvents = True 
    Exit Sub 
     
errHandler: 
    If Err.Number = 13 Or Err.Number = 1004 Then 
        Goto exitHandler 
    Else 
        Resume Next 
    End If 
     
End Sub 
 
Sub MyFix() 
    Application.EnableEvents = True 
     
End Sub 
 

I have also attached 2 workbooks to this post.

Thank you in advance!!

[Dave Hawley]

Why not use some simple lookup functions??

[tammeegoreng]

Hi Dave,

Thanks for the reply.

Actually the macro already existed from the person who originally made the invoice sheet, so I was trying to work from there.

How would I write the lookup function within the Listed cell?

Or is there a simple way to edit the existing macro?

[Dave Hawley]

Stick to formulas. Likely a few VLOOKUPS.