Sunday 9 December 2012

Pasting formulas between workbooks without workbook link

Have you ever needed to copy and paste a range of cells containing formula, each referencing cells in other worksheets, from one workbook to another ?  When you copy the formula across workbooks, Excel, by default, copies a link from the original file.  So instead of copying “=Sheet2!D6+Sheet2!E6” you end up with =[YourOriginalFile.xls]Sheet2!D6+[YourOriginalFile.xls]Sheet2!E6.

This has always frustrated me, I can see why it would be useful, but wouldn’t it be nice to have a simple way of copying the formula without the original file name.

For a while now I’ve been using a fairly simple work-around, using Edit / Replace.

  • Select the range you want to copy
  • Edit->Replace
  • What:= “=” (the equal sign)  /  With:=”^^” (or some unique string)
  • Replace All
Now the formula are text strings, these can be copied and pasted without Excel doing anything to “help” you.
  • Select the new range
  • Edit->Replace
  • What:= “^^” (or your unique string) / With:=”=” (equal sign)
  • Replace All

Now the text strings are formulas again.  You will need to ensure you have matching worksheets in that new workbook, otherwise you’ll be dismissing lots of dialogs.

7 comments:

  1. Thank you. This really helped. Best explanation ever.

    ReplyDelete
  2. Wonderful. Had spent hours looking for a solution. This worked perfectly. Thanks
    Femi

    ReplyDelete
  3. That was awesome... thanks!

    ReplyDelete
  4. Wen I udo the above it replaces with an ' on the front but will not remove that. What causes that?

    ReplyDelete
  5. Very Helpful :-)

    ReplyDelete
  6. I have a simpler solution that I tested and found worked.
    -- Copy the cells from the first workbook and paste into the second one as usual
    -- From one of the newly-pasted cells, copy the offending text you want to remove and hit escape button
    -- Using the "Find & Select" function, select "Replace."
    -- Paste the offending text you copied from the sample cell and paste it into the "Find what" window
    -- Leave the "Replace with" window blank
    -- Click "Replace" or "Replace All"
    -- Voila, no more workbook name in the cells

    ReplyDelete
  7. I have a simpler solution that I tested and found worked.
    -- Copy the cells from the first workbook and paste into the second one as usual
    -- From one of the newly-pasted cells, copy the offending text you want to remove and hit escape button
    -- Using the "Find & Select" function, select "Replace."
    -- Paste the offending text you copied from the sample cell and paste it into the "Find what" window
    -- Leave the "Replace with" window blank
    -- Click "Replace" or "Replace All"
    -- Voila, no more workbook name in the cells

    ReplyDelete

Please, no purely anonymous comments, they will be deleted; always use a name for ease of reference by other commenters.