使用 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. 首先，制作将生成公式的公式，但不要在开头添加 =！

让我们假设您已经在 Sheet1 的单元格 B2 中创建了此公式，并且您希望在列 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屋！