Worksheet links
 

I wish to copy a formula to another workbook but need to break the link so
the formula refers to a worksheet by the same name in the new workbook ie: I
wish to paste a worksheet containing formulas into a number of other
workbooks and that new worksheet has look-up formulas that refer to
worksheets of the same name in each workbook.

Hi
one way:
- make a backup of your source file :-)
- in your source sheet replace the equation sign with $$$$ ('Edit -
Replace')
- copy your source sheet to its new location
- in the target location replace '$$$$' with an equation sign '='

--
Regards
Frank Kabel
Frankfurt, Germany

"Bruce the Battler" <Bruce the
schrieb im Newsbeitrag
...
I wish to copy a formula to another workbook but need to break the
link so
the formula refers to a worksheet by the same name in the new
workbook ie: I
wish to paste a worksheet containing formulas into a number of other
workbooks and that new worksheet has look-up formulas that refer to
worksheets of the same name in each workbook.

Thanks Frank, but that means I have to edit each and every formula in the
target location. If I have to do that then I can just as easily remove the
reference to the source from the same formula.

"Frank Kabel" wrote:

Hi
one way:
- make a backup of your source file :-)
- in your source sheet replace the equation sign with $$$$ ('Edit -
Replace')
- copy your source sheet to its new location
- in the target location replace '$$$$' with an equation sign '='

--
Regards
Frank Kabel
Frankfurt, Germany

"Bruce the Battler" <Bruce the
schrieb im Newsbeitrag
...
I wish to copy a formula to another workbook but need to break the
link so
the formula refers to a worksheet by the same name in the new
workbook ie: I
wish to paste a worksheet containing formulas into a number of other
workbooks and that new worksheet has look-up formulas that refer to
worksheets of the same name in each workbook.

<"I have to edit each and every formula in the target location"

NO ... you don't!

Frank meant for you to select *all* your formulas at once, or, select the
entire sheet, and *then* follow his suggestion to use <Edit <Replace.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bruce the Battler" wrote in
message ...
Thanks Frank, but that means I have to edit each and every formula in the
target location. If I have to do that then I can just as easily remove the
reference to the source from the same formula.

"Frank Kabel" wrote:

Hi
one way:
- make a backup of your source file :-)
- in your source sheet replace the equation sign with $$$$ ('Edit -
Replace')
- copy your source sheet to its new location
- in the target location replace '$$$$' with an equation sign '='

--
Regards
Frank Kabel
Frankfurt, Germany

"Bruce the Battler" <Bruce the
schrieb im Newsbeitrag
...
I wish to copy a formula to another workbook but need to break the
link so
the formula refers to a worksheet by the same name in the new
workbook ie: I
wish to paste a worksheet containing formulas into a number of other
workbooks and that new worksheet has look-up formulas that refer to
worksheets of the same name in each workbook.

Another option is to "Edit Links" after you've completed the copying.
<Edit <Links <ChangeSource,
And click on the current WB name in the "ChangeLinks" window.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
<"I have to edit each and every formula in the target location"

NO ... you don't!

Frank meant for you to select *all* your formulas at once, or, select the
entire sheet, and *then* follow his suggestion to use <Edit <Replace.
--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Bruce the Battler" wrote in
message ...
Thanks Frank, but that means I have to edit each and every formula in
the
target location. If I have to do that then I can just as easily remove
the
reference to the source from the same formula.

"Frank Kabel" wrote:

Hi
one way:
- make a backup of your source file :-)
- in your source sheet replace the equation sign with $$$$ ('Edit -
Replace')
- copy your source sheet to its new location
- in the target location replace '$$$$' with an equation sign '='

--
Regards
Frank Kabel
Frankfurt, Germany

"Bruce the Battler" <Bruce the
schrieb im Newsbeitrag
...
I wish to copy a formula to another workbook but need to break the
link so
the formula refers to a worksheet by the same name in the new
workbook ie: I
wish to paste a worksheet containing formulas into a number of other
workbooks and that new worksheet has look-up formulas that refer to
worksheets of the same name in each workbook.

Thanks very much - problem solved.
Bruce

"Ragdyer" wrote:

Another option is to "Edit Links" after you've completed the copying.
<Edit <Links <ChangeSource,
And click on the current WB name in the "ChangeLinks" window.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
<"I have to edit each and every formula in the target location"

NO ... you don't!

Frank meant for you to select *all* your formulas at once, or, select the
entire sheet, and *then* follow his suggestion to use <Edit <Replace.
--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Bruce the Battler" wrote in
message ...
Thanks Frank, but that means I have to edit each and every formula in
the
target location. If I have to do that then I can just as easily remove
the
reference to the source from the same formula.

"Frank Kabel" wrote:

Hi
one way:
- make a backup of your source file :-)
- in your source sheet replace the equation sign with $$$$ ('Edit -
Replace')
- copy your source sheet to its new location
- in the target location replace '$$$$' with an equation sign '='

--
Regards
Frank Kabel
Frankfurt, Germany

"Bruce the Battler" <Bruce the
schrieb im Newsbeitrag
...
I wish to copy a formula to another workbook but need to break the
link so
the formula refers to a worksheet by the same name in the new
workbook ie: I
wish to paste a worksheet containing formulas into a number of other
workbooks and that new worksheet has look-up formulas that refer to
worksheets of the same name in each workbook.