Linked values turn to #REF! after clicking don't update

[John Perkins]

I have an excel spreadsheet that has linked values in it. I do not have
access to the source files. However when I open the document, I can see that
excel has filled in values for all of the cells. Then I immediately get the
"Do you want to update the links?" dialog. I click don't update hoping to
keep the values that I see. As soon as I click don't update, all the cells
turn to #REF!.

Excel knows what the values are that I want, they're there somewhere. But
how do I get them back?

Thanks.

 

[hrlngrv - ExcelForums.com]

John Perkins wrote...

I have an excel spreadsheet that has linked values in it. I do not have
access to the source files. However when I open the document, I can
see that excel has filled in values for all of the cells. Then I immediately
get the "Do you want to update the links?" dialog. I click don't update
hoping to keep the values that I see. As soon as I click don't update, all
the cells turn to #REF!.

Excel knows what the values are that I want, they're there somewhere.
But how do I get them back?

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
following

="="&SUBSTITUTE("'"&CELL("Filename")&"'!"
&CELL("Address"),".copy.",".")

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

='C:\foo\[A.xls]SomeSheet'!X99

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
in Excel.

 

[John Perkins]

Fantastic! Worked like a charm.

Many, many thanks.

John Perkins wrote...

I have an excel spreadsheet that has linked values in it. I do not have
access to the source files. However when I open the document, I can
see that excel has filled in values for all of the cells. Then I immediately
get the "Do you want to update the links?" dialog. I click don't update
hoping to keep the values that I see. As soon as I click don't update, all
the cells turn to #REF!.

Excel knows what the values are that I want, they're there somewhere.
But how do I get them back?

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
following

="="&SUBSTITUTE("'"&CELL("Filename")&"'!"
&CELL("Address"),".copy.",".")

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

='C:\foo\[A.xls]SomeSheet'!X99

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
in Excel.