How to stop Excel from updating worksheets copied from other files

Question

I have an Excel file, containing some sheet with some formula. I copied one sheet to another file and kept it on the same computer.

Now, if I change any formula or value in the master sheet, the copied sheet is also changed. I don't want this – so how can I disable that?

Answer 1

When you copied the sheet it kept the reference to the other sheet in the formulas like this;

=SUM([Budget.xls]Annual!C10:C25)

The [Budget.xls]Annual! refers to another file and sheet. You will need to remove these references to leave only the formula =SUM(C10:C25)

You can use Find and Replace to remove them.

Note: Backup your work. Use at your own risk. Not responsible for lost data.

1. On the Edit menu, click Replace.
2. In the Find what box, type refrence to the other sheet, [Budget.xls]Annual!, and leave the Look in box empty.
3. In Options choose to Look in formulas.
4. Click Replace All.