sharing data between work books

**the-bears** · 01-14-2010, 12:43 PM

I am trying to use a parts database to help complete a bill of materials. My db looks like this:

Part Number, Description , Material Specs
060001 , 1/4" hex nut, Zinc
060002 , 1/4" hex nut, Self colored
060003 , 3/8" hex nut, Zinc
060004 , 3/8" hex nut, Self colored

In my Bill of Material workbook I would like to beable to type in the Part Number in Column A and have it retrieve the information from the db in the other work book. So if I type in 060004 in A2, B2 would get the 3/8" hex nut form the db workbook and B3 would get the Zinc form the db workbook. I dont know if dget and a if loop is the correct way to go, or if I will need v-basic......
any help would be great..

**JBeaucaire** · 01-14-2010, 01:27 PM

You can use VLOOKUP or INDEX/MATCH on an external workbook, I do it all the time. A regular VLOOKUP appears like so:

=VLOOKUP(A1, Sheet2!B1:D100, 2, FALSE)

The second parameter is the table of data reference. You can change that to include a workbook name, too.

=VLOOKUP(A1, [Book2.xls]Sheet2!B1:D100, 2, FALSE)

An INDEX/MATCH would be my preferred formula since it can reference entire columns while still remaining robust:

=INDEX([Book2.xls]Sheet2!C:C, MATCH(A1, [Book2.xls]Sheet2!$B:$B, 0))

**the-bears** · 01-14-2010, 02:52 PM

Is it posiable to match it to the part number, It would not help me if I had to look up what cell the part number was in in the db work sheet....

**JBeaucaire** · 01-14-2010, 05:12 PM

Sounds to me like you didn't try it.

Both suggested formulas are examples, not solutions. You have to edit the range parameters to match your data. I expect you would be matching to the Part Number column and drawing answers back from other columns. Both formulas do that.