Page 1 of 2 12 LastLast
Results 1 to 10 of 12
Like Tree2Likes

Copying formulas without the link

This is a discussion on Copying formulas without the link within the Excel Questions forums, part of the Question Forums category; When I copy a formula from one workbook to another I get a link to the original workbook i.e. =(0,06*'[Energi ...

  1. #1
    New Member
    Join Date
    Jun 2002
    Posts
    14

    Default

    When I copy a formula from one workbook to another I get a link to the original workbook i.e.

    =(0,06*'[Energi Fyn_2 - test.xls]skema2_soliditet_balance'!$F$26)

    Is there a way to copy the formula without the link to the workbook ([Energi Fyn_2 - test.xls]) but still keeping the reference to the sheet (skema2_soliditet_balance'!$F$26)?

  2. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default

    Does this help ?
    How to copy a formula from one sheet to another and keep the formula pointing to the first sheet.
    Description: Copying formulas between sheets in Excel.
    Last updated: November 25, 2001, Expires on May 26, 2002
    Email this article to a friend


    Well there are two ways. The first is easiest but not as flexible.

    Highlight the cell/s on first sheet
    Click on [Edit] menu and choose [Copy]
    Click on the sheet you want to copy formula to.
    Place cursor where you want to paste formula to.
    Click on [Edit] and choose [Paste Specialů]
    Click on [Paste Link] button.
    This will work as long as you want formula in second cell to change when you make changes the first cell. It is not flexible, but it works.

    The second way to do it is a little more involved.

    Highlight the cell/s on first sheet
    Click on [Edit] menu and choose [Copy]
    Click on the sheet you want to copy formula to.
    Place cursor where you want to paste formula to.
    Click on [Edit] and choose [Paste]
    Click on cell now to edit formula
    Add Sheetname! Just inside the opening brackets. (i.e. =SUM(A3:B3)
    To =SUM(Sheet1!A3:B3))

    While this requires a little more work, it is more flexible and far less time consuming then recreating all the formulas over again. To cut down on time you can copy and paste the sheet name into the new formulas.
    source
    https://iats.missouri.edu/servlets/k.../article/18101

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  3. #3
    DBA
    DBA is offline
    Board Regular DBA's Avatar
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    100

    Default

    Hi Henrik,

    Don't copy the cell but highlight and copy the formula from the formula bar. (Ctrl-C is easy to use for Copy).

    Go to the other workbook and paste to the formula bar (Ctrl-V)

    Regards,

    DBA

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    39

    Default

    You could copy all the formulas you want over then go to Edit -> Links a change the source file to your current sheet.

    Russell

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    538

    Default

    you can always use the replace afterwards eg.
    replace "[Energi Fyn_2 - test.xls]" with ""
    Colin

  6. #6
    Board Regular
    Join Date
    Aug 2009
    Location
    washington, dc
    Posts
    354

    Default Re: Copying formulas without the link

    Is there a way to do this in Excel 2007?

  7. #7
    New Member
    Join Date
    Feb 2010
    Posts
    1

    Default Re: Copying formulas without the link

    Sorry if my english is bad!
    excel 2007
    If you want to copy sheet from one workbook to another without link beeing added to formula try this.
    Go to original workbook select sheet that you want to copy then right click select move or copy. Then select destination workbook check create copy box then ok. After this go to destination workbook select sheet that you copied then go to Data - Connections - Edit links
    In this menu go to change source , then browse for workbook that you are working in. Select, OK and link is going to disapear from list then OK and that is it.
    I hope this is going to help.
    Again sorry about my english!
    Khaled040 and cdav7 like this.

  8. #8
    New Member
    Join Date
    Aug 2011
    Posts
    1

    Default Re: Copying formulas without the link

    Gexer has it right - I just did it. Nice job. This is an elegant solution and much easier than copying individual formulas. Changing the workbook source to the destination can quickly fix all the links in the worksheet.

  9. #9
    New Member
    Join Date
    Jan 2012
    Posts
    1

    Default Re: Copying formulas without the link

    Quote Originally Posted by Russell View Post
    You could copy all the formulas you want over then go to Edit -> Links a change the source file to your current sheet.

    Russell
    Just wanted to give credit where it is due. Funny how someone gives a great answer, thanks Russell, and someone comes many years later and gives the same answer a post or two later and everyone praises that person. Haha. So just wanted to thank Russell for coming up with a great suggestion to an issue I was having. Certainly much easier than copy/paste/edit paste method.

  10. #10
    Board Regular BenElliott's Avatar
    Join Date
    Jul 2012
    Location
    Mansfield, UK
    Posts
    100

    Default Re: Copying formulas without the link

    You could copy all the formulas you want over then go to Edit -> Links a change the source file to your current sheet.

    Russell
    It doesn't work. Does anyone have any ideas how I can move several xl2010 worksheets that are full of large/complex formulae into another workbook without xl putting references into those formulae to the old workbook?

    Thanks,

    Ben

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com