Pulling data into multiple cells from a Database in seperate workbook based on 1 criteria

So what i am looking to do is create 2 linked workbooks

1. which will be a database of items.

2. will be a form that calls details from the database when i type 1 criteria (in this example the invoice number).



e.g

in my database i have data for 'invoice number | price | length | region'.

in another workbook i want the various items such as price length and region to auto fill in designated cells having typed the invoice number into the first cell.

any suggestions as to the best method for this?

Here's the code written by JieJenn yesterday. He/She wrote a code that's doing something similar to what youre asking for, except the 2 sheets are in the same workbook.

I've altered the code a bit to work on two workbooks. There are many open questions remaining about your inquiry(are both files open, will the names always be the same, which range is supposed to be copied etc.). The code works - you can change it complete your needs. If you don't know how, I can give it a try, but please attach an example of what needs to be done where first :0

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long
Dim wsP As Worksheet
1 Application.ScreenUpdating = False

Set wsP = Workbooks("Book1.xlsm").Worksheets(1)

If Target.Column = 1 Then
    With wsP
    LR = .Cells(Rows.Count, 1).End(xlUp).Row
    If LR < 1 Then
        Application.ScreenUpdating = True
        Exit Sub
    End If

Workbooks("test.xlsx").Sheets(1).Range("A2:M50").ClearContents
    For i = 2 To LR
        If .Cells(i, 1) > 0 Then
            With Workbooks("test.xlsx").Sheets(1)
              
                .Cells(.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) = wsP.Cells(i, 1)
                .Cells(.Cells(Rows.Count, 2).End(xlUp).Row + 1, 2) = wsP.Cells(i, 2)
                .Cells(.Cells(Rows.Count, 3).End(xlUp).Row + 1, 3) = wsP.Cells(i, 3)
                .Cells(.Cells(Rows.Count, 4).End(xlUp).Row + 1, 4) = wsP.Cells(i, 4)
                .Cells(.Cells(Rows.Count, 5).End(xlUp).Row + 1, 5) = wsP.Cells(i, 5)
                .Cells(.Cells(Rows.Count, 6).End(xlUp).Row + 1, 6) = wsP.Cells(i, 6)
                .Cells(.Cells(Rows.Count, 7).End(xlUp).Row + 1, 7) = wsP.Cells(i, 7)
                .Cells(.Cells(Rows.Count, 8).End(xlUp).Row + 1, 8) = wsP.Cells(i, 8)
                .Cells(.Cells(Rows.Count, 9).End(xlUp).Row + 1, 9) = wsP.Cells(i, 9)
                .Cells(.Cells(Rows.Count, 10).End(xlUp).Row + 1, 10) = wsP.Cells(i, 10)
            End With
        End If
    Next i
                

    End With
End If
Application.ScreenUpdating = True
End Sub

Thank you for the fast response, Both files will be Open database probably via a network and keep the same names.

Attached examples

Database.xlsx

Form.xlsx

Regards.

Ah, your problem is somewhat different i see. Why don't you use a simple formula then (instead of vba?).

Form.xlsx

Like the one attached. If you need a vba procedure for any reason, I'll get one for you in an hour as I'm leaving work now

This is great, exactly what I'm trying to achieve.

i do find myself trying to make things more complicated than they have to be.

Do you think you could give me a quick rundown of the method you have used to get to this point.

=IFERROR(VLOOKUP(B3,[Database.xlsx]Sheet1!$A$2:$D$23,2,FALSE),B3 &" Not found")

i understand everything up until !$A$2:$D$23 what does this range do, i get the 2 is column 2 and false for an exact match but the range baffles me, i have seen the $A before which i believe is the prefix for finding data from another source?

Thank you

I'm trying to do the same thing. I've made a spreadsheet to verify my pay. I get paid on piece work. The amount of work I do is converted to points which is divided by the incentive hours I worked, This gives me a points per hour which is converted into a monetary rate. This scale is on the "Pay Scale" tab. On the "Template" tab, G3 gives me my hourly point rate. I want H3 to automaticly pull from "Pay Scale" the corresponding rate in Template G3. So if G3 = 71 I want H3 to pull Pay Scale B8 ($12.27) over. I tried using a Nested formula but it wouldn't allow me to put in enough entries. I will format the pay chart back into 2 columns if it makes it easier for the formula. The only reason I formatted it more like a table was so that I didn't have to scroll as much when I went looking for the corresponding rate to manually enter it into H3.

Note: If you look at sheet 1 you will see how I copy and paste the template for each new week, and see how the sheet works with data entered if it helps. Also, there are a couple other place where the point rate is calculated. I don't need the pay rate next to these yet, except maybe next to the first AHE C9. If for some reason the formula will be different in each location then I need both.

Note:I did all this spread sheet myself. But that's just about the extent of my knowledge. As I mentioned the nested formula above, I just learned that yesterday. And it took reading an example my Stepdad sent, reading help files, and experimenting.
If the answer is the same you gave the OP

=IFERROR(VLOOKUP(B3,[Database.xlsx]Sheet1!$A$2:$D$23,2,FALSE),B3 &" Not found")

Then I have no idea what any of that is.
No matter what the answer is if you could take the time to explain it to me instead of just giving me the formula that would be greatly appreciated

I got it

=VLOOKUP(G3,'Pay Scale'!A1:B137,2)

I little more digging was all I needed