Workbook linking question

[fullor]

I've been working with excel at work for about two months now. I figured out everything I know about Excel on my own while trying to do things, so I am by no means very good with this program yet.

My question is: How do I link information from one Excel workbook to another workbook without the data value changing if another row is added to the source workbook?

Background: At work, we have about 15 different Excel files. One of those files is a Master Price list which has all the prices for what we sell. The other excel files breakdown products into categories, then list technical data for each product. One of the data values listed is the price. Before now, if someone changed a value in the pricelist, I'd have to go in and manually change it on any excel file that had that product's information on it. I've been assigned to find a way to link the Price cells from the Master Price List to the other excel files that hold that products price and technical data. I thought I had figured out a way to link the data using copy then paste special and paste link into the excel sheet. This links the Master Price List prices do the other excel files fine.

My problem is the Master Price List had rows added or taken off of it occasionally when new products arrive, or old products get removed. When this happens, the links I made to the Master Price List are now displaying the price for the product above or below it depending if a line was taken out of added.

For example: I link abc.xls B:1 to masterpricelist.xls D:200. Now I have the price for my product from the master price list in B:1 of abc.xls. Everything is fine until someone comes in and adds another line to the masterpricelist.xls. Now the price for the product I need is in D:201 because it has been pushed down a row. However, abc.xls is still drawing B:1 from D:200 so it lists the price of another product.

I notice the values seem to update IF all spreadsheets are open when the Master Price List is modified. This isn't full proof though, because if someone forgets, all the prices are now wrong again. I don't have access to modify the Master Price List. Two other people change or add to the price list. Opening up all the workbooks that are linked to the Pricelist is not an option, because someone modifying the master price list would have to open about 15 excel files, otherwise the prices will get messed up.

Does anyone have any suggestions, or is there a way to link the cells so that is pulls that data from the cell even if it is shifted down or up by the addition of another row to the master price list? I spent about 13 hours changing all the prices in the other excel files to be linked to the master price list because I was told paste special would do it. Now with the addition of about 15 new listings in the Master Price List, every price is wrong in the excel files.

Thanks,

[mdbct]

Try using the VLOOKUP() function.
in the abc.xls book
=VLOOKUP(B1,'C:\[masterpricelist.xls]Sheet1'!$A:$D,4,0)

where B1 contains the id of the item. The id for the item would need to be in the A column of the masterpricelist.xls file. You would need to adjust the file location and the sheet name to match your set up.

[tnfire]

I have this same exact problem. I have a master price list that will have items added or removed from it occasionally. I have sixty workbooks that contain detailed data on mixes of multiple products from the master price list. All the corresponding prices throughout the sixty workbooks are linked to their specific slot on the master price list. But, if the sixty workbooks are closed and anyone adds a new row to the master price list, when I open any of the workbooks the values update but the referenced cell does not.

So if the price for Product 25 is located in cell K118, and two products are added in above K118, when I open any of my mix sheets that contain Product 25 the linked cell should be to K120. But it's not doing it that way. It is holding on to the referenced K118 cell and updating the value to the wrong price.

I've tried to implement the solution provided above, but I was unable to make it work. And honestly I have a hard time understanding it. I've played around with the VLOOKUP function but haven't been able to make it work. Linking the cell as I have has worked great, if I could just get it to adjust the reference as new rows are added to the source workbook.

I've figured out that it will update the workbooks when I add a row if all that are required are open when the addition is made. But my boss will not open sixty workbooks every time a change needs to be made to the master price list, which he controls.

Any ideas or suggestions are greatly appreciated.

[shg]

Welcome to the forum, tnfire.

Please read the forum rules about posting questions in other people's threads (and in general), and then start your own thread. Provide a link to this one if you think it's particularly relevant.