workbook links

[Russ]

When opening a workbook in Excel I sometimes get the error:

"This workbook contains one or more links that cannot be updated."

I know what the error means and can usually track down the offending
cells but my question is how do I avoid it?

I believe it's caused by cut/paste between workbooks where the
reference to the source workbook is maintained. It seems that this
reference is only maintained when the cut information refers to
another worksheet in the source workbook. So, if the source workbook
has a formula like:

=SUM(Sheet2!A1:C1)

When I paste it into a new workbook I get:

=SUM([SourceWorkbook]Sheet2!A1:C1)

This seems to occur even if I have a worksheet of the same name,
'Sheet2', in the destination workbook.

Is there any way I can indicate to Excel that the pasted cells should
refer to 'Sheet2' in the destination workbook rather than 'Sheet2' in
the source workbook?

Thanks.

 

[Dave Peterson]

If you're only doing one formula, then copy from the formulabar--and paste into
the formulabar.

If you have lots of cells, change the formulas to text.

Select all the cells
edit|replace
what: = (equal sign)
with: $$$$$=
replace all

Then do the copy

And then do the edit|replace in reverse -- in both the original worksheet and
the new worksheet.

When opening a workbook in Excel I sometimes get the error:

"This workbook contains one or more links that cannot be updated."

I know what the error means and can usually track down the offending
cells but my question is how do I avoid it?

I believe it's caused by cut/paste between workbooks where the
reference to the source workbook is maintained. It seems that this
reference is only maintained when the cut information refers to
another worksheet in the source workbook. So, if the source workbook
has a formula like:

=SUM(Sheet2!A1:C1)

When I paste it into a new workbook I get:

=SUM([SourceWorkbook]Sheet2!A1:C1)

This seems to occur even if I have a worksheet of the same name,
'Sheet2', in the destination workbook.

Is there any way I can indicate to Excel that the pasted cells should
refer to 'Sheet2' in the destination workbook rather than 'Sheet2' in
the source workbook?

Thanks.