How do I keep Cell Referencing in Excel if I replace sheet?

Question

I have a sheet with a bunch of data. I then have a different sheet that references multiple cells in that first sheet. If I want to delete the first sheet and replace it with an identical sheet (identical in every sense, ie sheet name, data type in each cell, format, etc, except for the actual text data in each cell), the references in the other sheet are lost, and all my cells produce a #REF! error.

Is there any way of preserving the references and replacing or overwriting the sheet, without having to manually cut and paste the information?

Thank in advance,

George

Answer 1

Here is a solution I like to work with:

1. Before deleting the old sheet right-click on the sheet name and move (not copy) the sheet you wish to delete to a new / empty workbook.
2. Now, all links within the original file are automatically converted to reference the newly created workbook and all these links show up in the Data tab (Excel menu) Connections ► Edit Links.
3. Now you can insert the new sheet you wish to reference and in the above menu you can change the link to reference the original file. So, you are essentially changing the reference to itself back again (thus removing the reference to the newly created external workbook).

Note, that in this solution the replacement sheet will have to have the same name when inserted. Yet, you can (of course) change the sheet name after the above process is completed.

I surely hope I explained it sufficiently. Yet, don't hesitate to let me know if you require additional explanations.