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
- 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.
Thank you. This really helped. Best explanation ever.
ReplyDeleteWonderful. Had spent hours looking for a solution. This worked perfectly. Thanks
ReplyDeleteFemi
That was awesome... thanks!
ReplyDeleteWen I udo the above it replaces with an ' on the front but will not remove that. What causes that?
ReplyDeleteVery Helpful :-)
ReplyDeleteI have a simpler solution that I tested and found worked.
ReplyDelete-- 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
I have a simpler solution that I tested and found worked.
ReplyDelete-- 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