John Perkins wrote...
I've read that this is how the new & improved versions of Excel
now work. One step forward, two setps back.
Depending on what the formulas containing these external references
look like, you may have to use two copies of the file, one to open
and in which to replace the formulas evaluating to #REF! and the
other to remain closed and to feed values to the first copy.
For example, if you received A.xls with problem links, and save it in
C:\foo, make a copy of it as C:\foo\A.copy.xls. Open the copy,
A.copy.xls. In each cell which evaluates to #REF! press [Delete] to
clear the cell, then [F2] to go into Edit mode and paste in the
press [F9] then [F4] three times *before* pressing [Enter]. This
should replace the original formula with a link to the unopened
'original' which if left unopened still contains the values you need.
The resulting formula in cell SomeSheet!X99 would be
Once you have the first of these on a given worksheet, copy that cell
and paste into others where needed. You'll have to copy the formula
above and paste it into at least one cell on each of the worksheets
in A.copy.xls because worksheet names are always treated as absolute
Use your Bodum and give up cold coffee for good!