当工作表被删除和替换时，如何保留公式对工作表的引用？
我是一个应用程序的作者，该应用程序在打开 Excel 工作簿之前，会删除并重新创建一些工作表，例如下面示例中的“Sheet1”。
同一工作簿中的其他工作表（例如 Sheet2）可能具有引用被替换的工作表的公式，如下所示：
=IF('Sheet1'!A9="","",'Sheet1'!A9)
不幸的是，当工作表被替换时，上述公式中对 Sheet1 的引用被破坏，变成
=IF(#Ref!A9="","",#REF!A9)
Can Anybody think a way to code这个公式不会失败吗？例如，它可以从 Sheet2 中的隐藏单元格或其他内容中获取工作表的名称？
我只是对 Excel 公式了解不够，不知道有哪些可能性。
TIA
I am the author of an application that before opening an Excel workbook, deletes and recreates some worksheets, like, for example "Sheet1" in the example below.
Other worksheets (say Sheet2) in the same workbook may have formulas that refer to the replaced worksheet, like this:
=IF('Sheet1'!A9="","",'Sheet1'!A9)
Unfortunately, the reference in the above formula to Sheet1 is broken when the sheet is replaced, becoming
=IF(#Ref!A9="","",#REF!A9)
Can anyone think of a way to code this formula so it doesn't fail? It could, for example, get the name of the worksheet from a hidden cell in Sheet2 or something?
I just don't know enough about Excel formulas to know what the possibilities are.
TIA
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用间接寻址和命名范围
1) 使用填充有文本“SheetN!”的虚拟单元格（即 Z1...Zn）对于要删除的每张工作表。
2) 为这些工作表中的所有外部可寻址单元格和范围定义范围名称
3) 在公式中，将引用替换为“间接”和命名范围的文字。
示例：
替换
为
其中“caca”是 Sheet1 中定义为 A:A 的命名范围，并且 Z1 包含“Sheet1!”
另一个示例：
替换
为
现在您可以删除 Sheet1 而不会出现引用问题。
当您添加新的 Sheet1 时，您应该重新创建（在 VBA 中）命名范围。
编辑：回答您对名称中带有空格的表格的评论
在这种情况下，您需要额外的帮助:)
使用这个：
“_”应该被删除，因为我将它们插入那里只是为了便于可视化单引号和双引号。
在单元格 Z1 中输入 Sheet1 的名称，不带引号和感叹号（因为它已在公式中）。
哈！
Use Indirect Addressing and Named Ranges
1) Use phantom cells (i.e Z1...Zn) populated with the text "SheetN!" for each sheet you want to delete.
2) Define range names for all your externally addressable cells and ranges in those sheets
3) In your formulas replace the references with "Indirect" and the LITERAL of the named range.
Example:
Replace
with
Where "caca" is a Named Range defined in Sheet1 as A:A, and Z1 contains "Sheet1!"
Another Example:
Replace
with
Now you can delete the Sheet1 without reference problems.
You should re-create (in VBA) the Named Ranges when you add your new Sheet1.
Edit: Answering your comment about Sheets with blanks in name
In this case you need extra-fu :)
Use this:
The "_" should be removed, as I inserted them there just for ease the visualization of the single and double quotes.
In the cell Z1 enter the name of Sheet1 without quotes and without exclamation mark (as it is already in the formula).
HTH!