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.