Results 1 to 4 of 4

Thread: How to use Paste Formulas between Excel Sheet without any link

  1. #1
    Join Date
    Jan 2012
    Posts
    17

    How to use Paste Formulas between Excel Sheet without any link

    I want to copy and paste several cells, each containing formulas, from one workbook to another. The formulas contain references to several different sheets and I want the paste to contain the formulas exactly as they are in the originating workbook. When I paste these cells into the destination workbook, however, the formulas have inserted in them links to the originating workbook file name. The only way I can see to solve this problem is to individually select each cell, copy its formula from the edit formula field and then paste it into the edit formula field in the destination workbook. There are a lot of cells to copy and paste, so doing this cell by cell is not very feasible. Does anyone have any suggestions? Is there an option in Excel to disable the workbook link appearing in the formulas in the target workbook?

  2. #2
    Join Date
    Jun 2011
    Posts
    487

    Re: How to use Paste Formulas between Excel Sheet without any link

    Check my way around for it, first in the source sheet, select the sheet, do edit > replace and in find what put equal sign =, then replace with use for instance ^^ (something that you are sure is not in the worksheet), then copy everything to the new workbook, then in the new workbook reverse replacement with = replacing ^^, then finally reverse replacement in the source workbook.

  3. #3
    Join Date
    Jun 2011
    Posts
    635

    Re: How to use Paste Formulas between Excel Sheet without any link

    I am using the below method. I select my range to copy edit|replace
    with: = (equal sign)
    with: $$$$$= (some unique string)
    replace all
    Now my formulas are just text. Then copy > paste
    Then clean up both ranges:
    select the range
    edit|replace
    what: $$$$$= (that same unique string)
    with: = (equal sign)
    replace all
    Now my text strings are formulas again. (Make sure you have worksheets in that new workbook that match up--else you'll be dismissing lots of dialogs!). I'd fix one formula manually just to test.

  4. #4
    Join Date
    Jul 2011
    Posts
    634

    Re: How to use Paste Formulas between Excel Sheet without any link

    Select the cell containing the formula to be copied. Check that the cell references used in the formula will produce the desired result. If required, change the type of cell reference, for example, if a formula is needed to always refer to a cell, then this reference should be changed to absolute or mixed reference. Open the Edit menu and choose the Copy option. Then select the cell or cells in which you want to copy the formula. Open the Edit menu again. To copy the formula click Paste format. To copy the formula only click Paste Special, and then click Formulas.

Similar Threads

  1. Replies: 5
    Last Post: 26-04-2012, 04:16 AM
  2. Replies: 2
    Last Post: 24-02-2012, 07:22 PM
  3. Replies: 2
    Last Post: 23-02-2012, 02:59 PM
  4. How to do Copy/Paste on protected work sheet in Microsoft Excel
    By Chini mao in forum MS Office Support
    Replies: 1
    Last Post: 14-01-2012, 06:58 PM
  5. How formulas are calculated in Excel?
    By The Recruiter in forum Windows Software
    Replies: 5
    Last Post: 26-03-2010, 03:39 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,659,291.39816 seconds with 17 queries