如何在Excel中的引用中将单元格用作文件名? [英] How to use a cell as a filename in a reference in excel?
问题描述
如果我有一个单元格" a2
",其中包含文件名/文件路径的一部分,则要在其中使用" sheet2.xlsx
"作为参考另一本工作簿,我该怎么做?
If I have a cell, "a2
" which has part of a filename/filepath, "sheet2.xlsx
" in it which I want to use as a reference another workbook, how can I accomplish this?
我尝试使用 INDIRECT
函数,但始终会出现参考错误.
I have tried using the INDIRECT
function but always get a reference error.
我正在尝试找到一种方法,用列 a
的电子表格列表来汇总电子表格列表,该列包含电子表格名称,另一列引用该工作表中的单元格.
I am trying to find a way to summarize a list of spreadsheets with column a
containing the spreadsheet name and another column referencing a cell inside that respective sheet.
推荐答案
如果所引用的工作簿是 open ,那么下面是一个从中获取值的示例:
If the referenced workbook is open, then here is an example of getting a value from it:
=INDIRECT( "'" & A1 & "[" & B1 & "]" & C1 & "'!" & D1)
其中的组件类似于:
请注意,我们已指定:
- 路径
- 文件名
- 工作表名称
- 单元格地址
如果文件未打开,您仍然可以使用 ExecuteExcel4Macro 检索数据(假设您使用的是Excel版本)支持此功能的).有关此类代码的示例,请参见:
If the file is not open, you can still retrieve data with a ExecuteExcel4Macro (assuming you have a version of Excel that supports this function) For an example of this type of code, see: