copy worksheet to several workbook

Question

I created a worksheet/spreadsheet with functions and references to cells in other worksheets/spreadsheets within same workbook to create a report of other worksheets within the same workbook.

I have lots of workbooks/spreadsheets on which I have to copy this worksheet lots of workbook, but I when I copy the worksheet to another workbook, excel create references to the workbook where I copied from.

Does anyone know how to avoid excel doing this?

Any help would be very much appreciated.

Regards.

Answer 1

Excel is trying to be helpful by keeping the formulas pointing back to the original workbook.

To stop it doing this: 1) "break" the worksheet - do a find/replace across the whole sheet for "=" and replace with something that you know you have not used anywhere ever. "##" might be a good option. 2) Now copy the sheet to where you want it. Excel does not recognise your formulas as formulas since they are now just text entries 3) un-break it using find/replace to restore the = signs. As long as your formulas refer to worksheet names which actually exist in this target workbook, you should be fine.

Answer 2

Your question is vague. Relative/absolute cell copying does generally perform as you describe. When you create a formula in excel, by default it is relative.

C1 = A1 + B1 --When copied the referenced cells can change = relative

C1 = $A$1 + $B$1 --When copied the cells do not change = absolute

In this case, the formulas themselves need to be updated to behave accordingly.