0

I have to copy 120 thousand formulas from one xls file to another. After a simple copy-paste, the original file name is inserted into the formula, not good. I tried to remove it with Data - Links, but there was an error. So I decided to replace the = sign with q1g, so it is not recognized as an equasion, copy-pasted it, and tried to change q1g back to =. The problem is, an open dialog window pops up for every single formula. After I hit escape, it works, but I have to click Replace All again. Obviously, I do not want to do this 120000 times. How can I copy formulas without difficulties?

Improve this question
2
  • Paste Special might help.
    – Karan
    Jan 7, 2013 at 0:10
  • Any reason you can't use file Save As to a new file then clear the content of the cells which you don't need or paste in the data you do need? That would leave the formulas intact.
    – CharlieRB
    Jan 7, 2013 at 14:22

1 Answer 1

Reset to default
1

If you want to copy the formulas, just copy them "normally", i.e. with creating the links in the first step. Then try either of the following:

  • In the Edit Links dialog, click Change Source and select the new file in which you pasted the formulas
  • Select all pasted cells (Ctrl-A) and then use the Search and Replace (Ctrl-H) dialog and replace the [old file.xlsx] with a blank string (make sure to click Replace All). Note: do this while the old file is still open. If the file is closed, you need to either open it or replace the full file name including the path (as you see it in the formula).
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .