When copying formulas from one workbook to another, how to make the formula to use the data from the new workbook?

OK, I know it sounds complicated, so I will try and elaborate:

I have a lot of files (one created every day) - let's call them W1, W2... Every file has the same number of sheets and their names are the same - S1, S2...

I have to add a new sheet to every file. I did that at one of the files, but when I copy the sheet (select everything and then paste it to the new file) the formulas contain the name of the first file: SUM([W1]S1!...), but I want it to say SUM(S1!...)

I guess it is easy, but can't figure it out, and google didn't help me either.

Thanks in advance.

2 Answers

You didn't say the version (pre or post ribbon), but in Excel 2010:

Go ahead and paste as you are, and then Data->Edit Links -> Highlight the W1 reference-> Change Source button

Point it at itself (W2: the target/new file), and the external links should be removed.

I realize this is an older post, but I had the same issue, coppied over 40k formulas that i developed in an expanding book over about 4 years... Here is the easiest solution I found. In the original book.

1. Use the CTRL + ~ (the lower case version... Stack is hiding it).combination to show all formulas.
2. Copy the entire sheet or range of cells.
3. paste to new sheet.
4. again with the CTRL + ~ ...
5. In the formula bar of one of the formulas pasted (any one) copy JUST the name.xls. 6.Paste into a find/replace dialog box, leave the replace empty, click replace all...
6. Hit CTRL + `... All formulas should be referencing the same way it was in the old workbook. Note: all tabs in this workbook need to be named the same as the old one if there are references between them.