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.