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

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

Post by Sm9obiBQZX » Fri, 03 Dec 2004 01:33:05

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.

 
 
 

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

Post by hrlngr » Fri, 03 Dec 2004 03:15:10

John Perkins wrote...
have
can
immediately
update
update, all
somewhere.

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.
---------
www.coffeecozy.com

Use your Bodum and give up cold coffee for good!

 
 
 

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

Post by Sm9obiBQZX » Fri, 03 Dec 2004 04:53:07

Fantastic! Worked like a charm.

Many, many thanks.