First of all I want to thank all of those responding to these questions.
Thank you so much for your time! You guys are livesavers!
Here's the issue that I'm having. A group of my users
is opening an Excel
2007 file that has external links to other Excel files. In addition, the
linked Excel spreadsheets contain secondary or tertiary links to other Excel
spreadsheets. Not all of the spreadsheets are in Excel 2007 format. Some of
them are Excel 2003. At some point in the process, one of the users began
updating the Excel 2003 files to Excel 2007 format. But, not all of them were
migrated. This caused the calculations in the main (for our example I am
calling it "Master") spreadsheet to fail. All Cells had the following error:
We initially thought it may be the links, so we cleaned up the Master
Spreadsheet's Links and verified them all. But, when trying to update the
links (either automatically by changing the setting to "enable automatic
updating of links") or manually (by clicking Links on the data ribbon and
manually updating them) the links showed an OK status, but the values in the
Master Spreadsheet's cells still contained the error "#VALUE!".
I ran across a suggestion in another thread to
open all the linked
spreadsheets and the master spreadsheet and then click CTRL+ALT+F9 to force a
recalculation of all cells in all spreadsheets. This did update the Master
Spreadsheet with the correct values and the error went away. However, when
you attempt to make any changes to the Master Spreadsheet and save the
changes, the error reappears and the calculations do not work.
I have not tried to open every file past the first
level of links at the
same time as of yet. I may do that next. I know for a fact that there is the
Master Spreadsheet with at least three levels of linked spreadsheets or more.
And I know that the linked spreadsheets contain both versions of Excel.
My question is, how do I fix this so that
it quits breaking every time
changes are made to the master spreadsheet?
Thank you again to anyone that has any suggestions!