删除和替换工作表时，如何保留公式对工作表的引用？ [英] How to preserve a formula's reference to a worksheet when the worksheet is deleted and replaced?

问题描述

我是一个应用程序的作者，在打开Excel工作簿之前，删除并重新创建一些工作表，例如下面的示例中的Sheet1。

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.

相同工作簿中的其他工作表（称为Sheet2）可能具有引用替换的工作表的公式，如下所示：

Other worksheets (say Sheet2) in the same workbook may have formulas that refer to the replaced worksheet, like this:

=IF('Sheet1'!A9="","",'Sheet1'!A9)

不幸的是上面的公式中的Sheet1在Sheet被替换时被打破，成为

Unfortunately, the reference in the above formula to Sheet1 is broken when the sheet is replaced, becoming

=IF(#Ref!A9="","",#REF!A9)

任何人都可以想到一种方式来编码这个公式，不会失败？例如，可以从Sheet2中的隐藏单元格中获取工作表的名称吗？

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?

我只是不了解Excel公式，以了解什么是

I just don't know enough about Excel formulas to know what the possibilities are.

TIA

推荐答案

使用间接寻址和命名范围

1）使用文本SheetN！填充幻影单元格（即Z1 ... Zn）对于您要删除的每个工作表。

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）为这些表格中的所有外部可寻址单元格和范围定义范围名称

2) Define range names for all your externally addressable cells and ranges in those sheets

3）在您的公式用间接和指定范围的LITERAL替换引用。

3) In your formulas replace the references with "Indirect" and the LITERAL of the named range.

示例：

替换

    =Sum(Sheet1!A:A)   

与

    =SUM(INDIRECT(Z1&"caca"))  

其中caca是Sheet1中定义为A：A的命名范围，Z1包含Sheet1！

Where "caca" is a Named Range defined in Sheet1 as A:A, and Z1 contains "Sheet1!"

另一个例子：

替换

    =Sheet1!A1+Sheet1!A2

with

    =INDIRECT(Z1&"NamedRangeForA1")+INDIRECT(Z1&"NamedRangeForA2")  

现在您可以删除Sheet1而没有参考问题。

Now you can delete the Sheet1 without reference problems.

当您添加新的Sheet1时，您应该重新创建（在VBA中）命名范围。

You should re-create (in VBA) the Named Ranges when you add your new Sheet1.

编辑：回覆您有关名单中有空格的评论

案例你需要额外的费用:)

In this case you need extra-fu :)

使用这个：

 =INDIRECT("_'_" & Z1& "_'_!_" & "caca")  

应该删除_，因为我将它们插入到那里，以便于单引号和双引号的可视化。

The "_" should be removed, as I inserted them there just for ease the visualization of the single and double quotes.

在单元格Z1中，输入Sheet1的名称，不带引号，没有感叹号（因为它已经在公式中）。

In the cell Z1 enter the name of Sheet1 without quotes and without exclamation mark (as it is already in the formula).

HTH！

这篇关于删除和替换工作表时，如何保留公式对工作表的引用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！