如果替换工作表，如何在Excel中保持单元格引用? [英] How do I keep Cell Referencing in Excel if I replace sheet?

问题描述

我有一个包含大量数据的工作表.然后，我有另一个工作表，该工作表引用了第一工作表中的多个单元格.如果要删除第一张工作表并用相同的工作表替换(在每种意义上都是相同的，即工作表名称，每个单元格中的数据类型，格式等，但每个单元格中的实际文本数据除外)，其他表丢失了，我所有的单元格都产生了#REF！错误.

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?

预先感谢

乔治

推荐答案

以下是我喜欢使用的解决方案:

Here is a solution I like to work with:

1. 在删除旧工作表之前，右键单击工作表名称，然后将要删除的工作表move(不复制)单击到新的/空的工作簿中.
2. 现在，原始文件中的所有链接都将自动转换为引用新创建的工作簿，并且所有这些链接都显示在Data选项卡(Excel菜单)Connections►Edit Links中.
3. 现在您可以插入要参考的新图纸，并且在上面的菜单中可以更改链接以参考原始文件.因此，您实际上是在重新更改对自身的引用(从而删除了对新创建的外部工作簿的引用).

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.

这篇关于如果替换工作表，如何在Excel中保持单元格引用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！