Links updating when new row is entered into source

**tnfire** · 01-23-2009, 08:25 PM

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. The problem: 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 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** · 01-23-2009, 08:45 PM

You should absolutely not use fixed references -- it's an invitation to disaster.

How about stripping the master and one example of the other 60 down to reasonable size and posting.

**tnfire** · 01-26-2009, 12:46 PM

Attached is a stripped down version of the Master Price List, and one of stripped down example of a mix detail workbook with only the cost sheets included. In the mix workbook I am trying to link the "Cost Per Pound" column with their respective spot in column D on the Master Price list. I have this done, but I need the mix workbooks to update when they are closed and a row is added to the Master Price List. I will listen to any ideas or thoughts about how this might be done. If there is any area where clarification should be provided on my part, please let me know. Thank you.

Note: In case anyone is wondering, the NPA that is plugged into the IF formula in column D represents 'No Price Available', to display when that is the case for that product. That NPA inclusion is not necessary, and is secondary to the goal of getting the products to link to the master price list how I need them to.

**tnfire** · 01-27-2009, 01:34 PM

Bump, no response.

**tnfire** · 01-29-2009, 01:13 PM

Bump, no response.

**tnfire** · 01-30-2009, 02:58 PM

Is there really no one here who can help me? If somebody with some experience tells me that this can't be done, then I'll move on. But I have a hard time believing that this is the limit of Excel's capabilities with what I am trying to do. Shg, you asked for me to upload examples and I provided them. Were they not helpful? Is more information needed? Give me a little direction and I can try to work through this with someone who is willing to lend a hand, or tell me if it is out of the league of expertise available on this site. Either way, if anyone is able to take the time and provide an answer I thank you for it.

**shg** · 01-30-2009, 03:32 PM

Change the formula in E5 to

=VLOOKUP(A5, '[Master Price List Example.xls]Master Price List -09'!$B:$D, 3, FALSE)

... and copy down.

This returns only two correct results because most of the items in column A have no exact matches with the data in Master column B. When you correct that, it will be fine.

**tnfire** · 01-30-2009, 06:18 PM

I cannot thank you enough. Since the examples I provided you with are stripped down versions it took a little bit of tweaking, but I got it figured out. I am in the process now of copying down that formula to all the mix sheets, and changing all the products names to match identically between them. Again thank you for taking the time to help me out and make life around here much more simplified. Hopefully anyone with a similar problem will benefit from this thread.

**shg** · 01-30-2009, 07:36 PM

You’re welcome.

Spreadsheets should be designed to be robust and as broadly tolerant of changes in data as possible. If one isn't, you should be thinking about why not, and how to change it to make it so. Excel certainly has the capabilities to support good design.

Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes