Excel 2007 - Invalid Cell Reference when using Edit Links for external cell references

Hi, I am in dire need of help. I have a very weird excel problem that I can't figure out. Some direction would be very helpful. Here is the story:

I have 3 spreadsheets in total. 1 (let's call this the parent spreadsheet) is a master spreadsheet that needs to bring in data from the other 2 (let's call these the children). Originally it was just 2 (parent and 1 child) spreadsheet and it worked fine. What I would have to do was open both spreadsheets in the same instance of excel; just having the child spreadsheet behind the parent. I would use Edit Links and bring in the daily child spreadsheet. I would need to update the child spreadsheet on a daily basis. The formula used to bring in the child data was SUMIF. This process worked fine.

The problem I am having is with the second child spreadsheet. The second child spreadsheet is using a SUMPRODUCT and SUMIF formula to bring in data. The data is brought in fine when you create it. The formula works. The problem comes the next day when I have a new child spreadsheet (updated data) and I try to use edit links to bring it in. Excel indicates that there is an invalid cell reference and it can't be brought in. Now when this happens I have both updated children open in the same instance of excel. However, when I close out of the second child and then use edit links to bring it in, it works fine. Edit Links updates and then I have to bring in the second child to fill in the VALUES#. Once I’ve brought it in then the values are updated. So, I know it’s not the formulas because they work and they update.

Why am I getting “invalid cell reference…etc” when I use edit links and have all 3 spreadsheets open in the same instance? The 2 children are the updated versions. The older child updates just fine when I use edit links the first time. The newer child is getting this error message until I close out of it, bring it in through edit links, and then re-open.

Now here is my question....how do I bring in the second child without re-opening it every time? The goal is to have both children open in the same instance as the master spreadsheet and just use edit links to bring in the data. I'm constantly using the data from the children to update different parent spreadsheets. So closing out and re-opening the new child every time will become very tedious.

Help and guidance would be greatly appreciated. Thank you!

I can not take that option.