使用INDIRECT在封闭的Excel工作簿中引用值？ [英] Referencing value in a closed Excel workbook using INDIRECT?

问题描述

我想在另一个 关闭 工作簿中使用公式（而不是VBA！）引用单元格值。工作表名称存储为变量（在下面的示例中，C13是Sheet2）。

I want to refer to a cell value in another closed workbook with a formula (not VBA!). The Sheet name is stored as a variable (in the following example, C13 is "Sheet2").

如果其他文件打开，则以下工作：

If the other file is open, then following works:

=INDIRECT("[myExcelFile.xlsm]" & C13 & "!$A$1")

如果文件关闭，上述公式不起作用，因为没有给出绝对路径。但是我得到了以下的工作（注意而不是：

If the file is closed, the above formula doesn't work, as there is no absolute path given. But I got it work with following (give attention to ' instead of ":

='C:\data\[myExcelFile.xlsm]Sheet2'!$A$1

现在我要将硬编码的Sheet2替换为一个动态引用的值，意思是C13（如第一个代码片段所示）。

Now I want to replace the hardcoded "Sheet2" with a dynamic referenced value, means with C13 (as seen in the first code snippet).

有没有人知道一个解决方案，而不使用VBA或其他库？

Does anybody know a solution without using VBA or other libraries?

推荐答案

有明确的没有办法这样做与标准公式，但是，一个疯狂的答案可以找到这里，它仍然避免了VBA，它将允许您动态地获得结果。

There is definitively no way to do this with standard formulas. However, a crazy sort of answer can be found here. It still avoids VBA, and it will allow you to get your result dynamically.

1. 首先，创建生成公式的公式，但不要添加 = 开始！

让我们假设您已在 B2 中的 Sheet1 ，您希望在 c 中对公式进行评估。

Let us pretend that you have created this formula in cell B2 of Sheet1, and you would like the formula to be evaluated in column c.

现在，转到公式选项卡，然后选择定义名称。给它名字 myResult （或任何您选择的），并在指定下，写入 = evaluate（Sheet1！$ B2）（注意 $ ）

Now, go to the Formulas tab, and choose "Define Name". Give it the name myResult (or whatever you choose), and under Refers To, write =evaluate(Sheet1!$B2) (note the $)

最后，转到 C2 ，并写入 = myResult 。向下拖动，然后... 瞧瞧！

Finally, go to C2, and write =myResult. Drag down, and... voila!

这篇关于使用INDIRECT在封闭的Excel工作簿中引用值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！