未打开Excel文件的动态INDEX函数引用失效问题求助

静态INDEX能直接引用未打开外部文件的单元格，但**INDEX不接受文本格式的引用路径**——CONCAT生成的只是字符串，无法被INDEX识别为有效单元格区域；而INDIRECT函数本身不支持引用未打开的外部Excel文件，所以两种方式组合后都会返回#REF!错误。

方案1：Power Query（推荐，支持未打开文件） #

这是最稳定的解决方式，无需打开目标文件即可动态提取数据：

1. 打开趋势表工作簿，点击「数据」选项卡 → 「获取数据」→ 「从文件」→ 「从文件夹」
2. 在弹出对话框中输入文件夹路径\\FileServer\Folder1\Folder 2，点击「确定」
3. 在Power Query编辑器中，点击「添加列」→ 「自定义列」，输入公式匹配文件名（假设趋势表A列是日期）：

   = "Output Spreadsheet " & Text.From([YourDateColumn], "dd mmm yy") & " _ Test Template.xlsx"
   

   替换[YourDateColumn]为实际的日期列引用（比如当前查询表的Date列）
4. 添加筛选列，匹配「名称」列和自定义生成的文件名，只保留目标文件
5. 点击「添加列」→ 「自定义列」，输入公式加载目标工作表数据：

   = Excel.Workbook([Content]){[Item="Roll-Up",Kind="Sheet"]}[Data]
   

6. 展开自定义列，选择需要的A:T列，调整数据格式后，点击「关闭并上载」将数据加载到工作表
7. 后续只需点击「数据」→ 「全部刷新」，即可根据最新日期自动提取对应文件的数据

方案2：仅当目标文件可打开时使用（INDIRECT+辅助单元格） #

如果可以确保目标文件处于打开状态，可通过以下方式实现：

1. 在辅助单元格（比如B1）中用CONCAT生成完整路径：

   =CONCAT("'\\FileServer\Folder1\Folder 2\[Output Spreadsheet ",TEXT(A1,"dd mmm yy")," _ Test Template.xlsx]Roll-Up'!A:T")
   

2. 使用INDIRECT将文本路径转为有效区域，再配合INDEX：

   =INDEX(INDIRECT(B1),2,1)
   

   注意：此方法要求目标文件必须打开，否则仍会返回#REF!

内容的提问来源于stack exchange，提问作者talofaman