Linking lists across workbooks

**ajfreed** · 01-20-2009, 02:12 PM

I am trying to create a master price list, where the 1st file called MasterPriceList will list all of our ingredients and their prices.

It would look like this:
A_________B
ING______Price
1 Ing 1___$1.00
2 Ing 2___$1.50
3 Ing 3___$2.00
4 Ing 4___$3.00

My second workbook is is a template for when we need to formulate blends.
It looks something like this:
A______B______C_______D__
1 Ing 1__50%__$1.00__$0.50
2 Ing 3__50%__$2.00__$1.00

We want to pull the cost of the ingredient from the MasterPriceList and populate column C with that value.

I have tried copying and pasting a link. This works fine until I insert a row. Even If I make the link relative (='[MasterPriceList.xls]Sheet1'!$C1), it will only adjust if both worksheets are open at the same time. Since we will have over 200 pricing sheets, it would be impractical to have them all open every time we have to insert a new ingredient into the MasterPriceList workbook.

I have tried to use VLOOKUP but it will not work across workbooks and the same is true with Drop Down lists. I read a tutorial, http://office.microsoft.com/en-us/ex...995141033.aspx , which details how to create a drop down list across workbooks but when I come to the step of defining the validation list, I get a message saying that it can't be done across workbooks.

Any ideas? I just want to be able to, with drop down or not (drop down is preferable, but not necessary) create a master sheet which will link to cells in many other workbooks, which will update if I insert a new row into the master file.

Thanks in advance for any suggestions.

Allen

**JBeaucaire** · 01-20-2009, 03:07 PM

VLOOKUP works fine off of other workbooks. Here's an excerpt of one of my VLOOKUPs that is pulling data from a separate sheet:

=VLOOKUP(C4,'C:\Documents and Settings\Jerry2\My Documents\!TUXX\Customer Docs\Paulino\[!MASTER Store List.xls]Sheet1'!$C$7:$F$145,4,FALSE)

**ajfreed** · 01-20-2009, 03:14 PM

Thanks, I will try that. Looks like what I tried previously but I probably had a typo or the structure was incorrect.

I'll let you know if I can get it to work.

Thanks again.

Allen

**JBeaucaire** · 01-20-2009, 03:42 PM

Originally Posted by ajfreed

Thanks, I will try that. Looks like what I tried previously but I probably had a typo or the structure was incorrect.

I'll let you know if I can get it to work.

Thanks again.

Allen

When in doubt, make the links with both workbooks open. Then you don't have to type anything, just use the mouse to highlight the range in the second book.

**ajfreed** · 01-20-2009, 07:06 PM

Thanks for the help, I did get it to work, I must have been entering the formulas incorrectly.

Now I am having a similar problem with Drop Down Lists across the sheets.
Using the same two workbooks, I now want the area that is my list in the MasterPriceList.xls file to be drop down in the other sheet.

When I enter the formula in the data validation "refers to" cell, I just get back the text string, i.e., =C:\Documents\Allen\Documents And Settings\etc.

Also, if I try, as mentioned above, keeping the other worksheet open and highlighting the cells, it does the same thing.

I tried naming the range in the MasterPriceList.xls sheet and using the path and range name, but I still get a text string repeating the path, as a result.

Any ideas.

Again, I truly appreciate the responses.

Allen