match product id with product name from other workbook

I need help creating a function that will link one workbook to another. If you look at my two workbooks.


http://www.excelforum.com/attachment...1&d=1216415658

http://www.excelforum.com/attachment...1&d=1216415658

One workbook is a spreadsheet with a list of products and their I.D. numbers. It also includes their weight per unit.

The other workbook is a bill of lading that we use to ship our product. I want to be able to type in the I.D. number on attachment BOLtest2 in cell D14 and wherever else I have an ID number. These items change for each shipment. Also sometimes more product or less product will be added.

I want to be able to type that I.D. number in and when I hit enter have the name of the product that corrospondes to that ID number automatically pop up in cell E14. Also when that shows up, I want it to bring the weight per unit over from the flavor list workbook. I want it to then multiply that by the number of units that I am sending in B12 and calculate the weight in J12

Is this possible to do, if so how... Thanks for the help.

Hi,

Can you save as a non Excel 2007 file, i.e. a .xls ? You'll no doubt receive a larger response.

Rgds

Here you thanks. Thanks for the heads up.

Anybody, I need help with this. Its killing me. I cant figure it out,

Try Vlookup. What is the ID no. Is it from column-B and C in Flavor list. Why can't you use a single column for it.
Best wishes
Sreedhar

=VLOOKUP(D14,'[Flavor List.xls]Sheet2'!$A$5:$C$13,2,FALSE)

Originally Posted by flex

=VLOOKUP(D14,'[Flavor List.xls]Sheet2'!$A$5:$C$13,2,FALSE)

It came up with error #N/A. Would it better to combine the product code wit the flavor?

Originally Posted by NBVC

L-1156 (which is in D13) does not show up in the flavour lists in Sht1 nor Sheet1 of the Flavor.xls workbook. That may be why you are getting #N/A

Why is it not showing up? How do I change this?

Would it be better to create a drop down menu, So where I want to enter the Flavor name, I can select the name from a drop down menu. I would also need the specific weight tied to that Flavor name. I dont know how to do this though. I am pretty much a rookie when it comes to this sort of thing.

Originally Posted by NBVC

For Vlookup to give you a result, you have to have the items to be looked up in the lookup table...

You provided a lookup table in the Flavor.xls workbook. But the item you are looking up in the BOL test workbook is not in that table so it can't pull any info.

How would I go about changing the BOL for this to work correctly?

Originally Posted by NBVC

I am not sure if the BOL that needs to change or the Flavors sheet that needs to change... it depends on if you want to limit the possible codes in BOL to what you have in your list.

If you want to only be able to input codes that are in your flavour list, then you will need to created a named range and use Data Validation to create the drop down list of choices.

First, you have to transfer the list into the BOL workbook by copy pasting the flavour codes only. Then select the range and type in a name like FlavorCodes in the field to the left of the formula bar at the top of the spreadsheet.

Then go D13 and go to Data|Validation and select List from the Allow menu.

Enter =FlavorCodes in the Source field.

Click Ok.

Now you should have a drop down in D13 with the flavour codes. Once you select one, the Vlookup should bring the actual flavour up from the Flavor.xls sheet.

Please bear with me, I am very new to this. Would you mind explaining that again with just a little more detail? I would very much appreciate it. Thank you.

Wow thats great! Thank you so much. Are the specific weights tied to each name also? Is there a way to do that?

Also I was looking at it, how do I add more L-numbers and flavors?

Originally Posted by NBVC

Just add them to the lists in the Flavor workbook.

Then add them to the range in column U on the BOL sheet...

Then go to insert|Name|Define... select FlavorCodes and change the range in the Refers to field to this formula: =OFFSET($U$1,0,0,COUNTA($U:$U),1)

Okay, well thanks for your help. It will be alot easier now.

I am stll having trouble. I am working on Excel2007. So when I go to the ribbon at the top and select insert, there is no option to select name then define. Is there another way?