| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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. |
|
#2
|
|||
|
|||
|
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. |
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
<"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. |
|
#5
|
|||
|
|||
|
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. |
|
#6
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copyright and worksheet protection | Excel Discussion (Misc queries) | |||
| Executing macro for all worksheet from a different worksheet | New Users to Excel | |||
| Reference Data in Moved Worksheet | Setting up and Configuration of Excel | |||
| Worksheet name and Backward compatibility | Excel Discussion (Misc queries) | |||
| hyperlinks and copying a worksheet | Excel Worksheet Functions | |||
Linear Mode
