inadvertent external links when copy/pasting

[JB]

Suddenly when I copy/paste between workbooks, the pasted formulas contain an
external link to the source workbook. Is there a setting I have changed
accidently, that is causing me to inadvertently creat external links?

 

[GSnyder]

Hi, JB.

One thing I can think of is if your formula in the first worksheet refers to
another sheet in that workbook, it will retain that link even if you copy it
to another workbook. For example, if you copy =$B$8 to another workbook, it
will simply copy over and refer to the new workbook. No problem.

But if you copy =Sheet2!$B$8 over to a new workbook, it will try to hang
onto its link to that worksheet in the original workbook and it will copy
over as =[Book1]!Sheet2!$B$8.

Could that be what's happening to you?

If so, you could use search and replace to find all instances of [Book1] and
replace them with nothing.

 

[JB]

Why yes, that is the case. So if I understand right, when my source workbook
has multiple sheets, this is what I can expect. Thanks for the clean up tip.
JB

Hi, JB.

One thing I can think of is if your formula in the first worksheet refers to
another sheet in that workbook, it will retain that link even if you copy it
to another workbook. For example, if you copy =$B$8 to another workbook, it
will simply copy over and refer to the new workbook. No problem.

But if you copy =Sheet2!$B$8 over to a new workbook, it will try to hang
onto its link to that worksheet in the original workbook and it will copy
over as =[Book1]!Sheet2!$B$8.

Could that be what's happening to you?

If so, you could use search and replace to find all instances of [Book1] and
replace them with nothing.

--
Happy calculating!

If you like this answer, please click ''Yes.''

Suddenly when I copy/paste between workbooks, the pasted formulas contain an
external link to the source workbook. Is there a setting I have changed
accidently, that is causing me to inadvertently creat external links?

 

[Shane Devenshire]

Hi,

It really has nothing to do with multiple worksheets, it is strictly because
your formulas refer to other sheets in the workbook.

You can use any of these methods to deal with the problem:

1. You can use the Find and Replace approach previously suggested by GSnyder
2. You can choose Edit, Links, Change Source and find and click on your
active sheets file name. This will reset all the links to the current
workbook.
3. You can select the formula on the Formula Bar and choose Copy, switch to
the other workbook and choose Paste. This is good for copying one formula
not a lot.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Why yes, that is the case. So if I understand right, when my source workbook
has multiple sheets, this is what I can expect. Thanks for the clean up tip.
JB

Hi, JB.

One thing I can think of is if your formula in the first worksheet refers to
another sheet in that workbook, it will retain that link even if you copy it
to another workbook. For example, if you copy =$B$8 to another workbook, it
will simply copy over and refer to the new workbook. No problem.

But if you copy =Sheet2!$B$8 over to a new workbook, it will try to hang
onto its link to that worksheet in the original workbook and it will copy
over as =[Book1]!Sheet2!$B$8.

Could that be what's happening to you?

If so, you could use search and replace to find all instances of [Book1] and
replace them with nothing.

--
Happy calculating!

If you like this answer, please click ''Yes.''

Suddenly when I copy/paste between workbooks, the pasted formulas contain an
external link to the source workbook. Is there a setting I have changed
accidently, that is causing me to inadvertently creat external links?