Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
Paste Special might help. –  Karan Jan 7 '13 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 '13 at 14:22

1 Answer 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).
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.