It can happen to the best of us. The links are broken and formulas no longer work in a spreadsheet.
How can it happen? A couple typical ways:
One Excel file is open but not the other. The layout is changed in the open file, such as adding a row. When the second workbook is open, it points to the same row.
An Excel file is renamed. Any linked files that are not open will not pick up on the change.
An Excel file is moved to a different location.
A file does not get saved and Excel crashes.
Broken links can be one of the hazards of doing business, but there are things you can do to minimize the risks.
Have all linked files open at the same time. This works best when there are only a couple files.
Save your files often.
Use Autosave if you are using Excel 2000 or earlier. Set it up to save all your open files. (Downside- you may not want all files saved- if so, make sure you have the files set up to save a copy of the file with every save).
Get Excel 2002. We hate to suggest spending money, but this feature has been worth its weight in gold to us in our work. Excel 2002 comes with AutoRecover capabilities, to save your work in event of a crash.
Know what your links are. Go to the Edit menu and select Links. Highlight a link and choose open link if applicable before you start your work.
Link between files using named ranges. This is a great benefit of using named ranges in your work. If you use named ranges, then you don't have to have the other file open. Your link should not get broken unless you change the name of the named range.
Don't rename or move files without knowing all the links first and what trouble you might be getting into.
If you are making major changes in files, consider using version numbers.
Let's use an example to cover the named range point, since it can be a lifesaver for your links.
Suppose you have a line for sales in row 10 of a Sales worksheet in a PL workbook, which you pick up in another workbook called Budget. Now suppose you have just the Sales workbook open and you insert a row in the Sales worksheet so sales is now in row 11. Then you close the PL workbook. Then you open the Budget workbook.
If you just linked to cells, then the Budget workbook will have zeroes for sales since it is looking at row 10 which is now a blank row in the other file.
If instead you linked to the named range "sales", then your link is still OK.
If you have never done links to a named range across files, try it on a sample test.
Suppose you have broken a link. It can be a fast trip to a messed up workbook. What to do then?
If not too extensive, use the Links feature under the Edit menu. You get a dialog box which allows you to change the source file.
You could manually fix links, perhaps using Search and Replace or having both files open and recreating the link.
Create named ranges and link to the named range, so next time you won't break.
In more extreme cases, you may be into damage control and have to determine how far back was your last clean link. Save the current broken file under a new name and pull up the last good version you know of.
Broken links are no fun but sooner or later they can happen. Use these techniques to keep them to a minimum.