Excel公式引用具有动态文件路径的另一个(封闭式)工作簿中的单元格 [英] Excel formula to reference cells in another (closed) workbook with dynamic file path
问题描述
这是我想要的结果:我想要一个Excel工作簿(例如Master.xls)，可以将其放入其他Excel工作簿的目录中，并且Master.xls将从中的所有其他数百个工作簿中提取给定的单元格范围该目录.我有多个目录，每个目录中都有数百个Excel文件，因此我需要一个Master.xls文件，该文件可以在具有不同文件路径的目录之间轻松移动，并根据目录中周围的文件进行更新.在我的Master.xls文件中，我可以使用诸如CONCATENATE之类的文本功能为所有其他工作簿建立文件名.
Here's my desired outcome: I want an Excel workbook (say Master.xls) that I can drop into a directory of other Excel workbooks and Master.xls will extract a given range of cells from all of the hundreds of other workbooks in that directory. I have multiple directories with hundreds of Excel files in each, so I need a Master.xls file that will easily move between directories with different file paths and update based on the files around it in the directory. In my Master.xls file, I can build the file names for all of these other workbooks using text functions like CONCATENATE.
当我尝试使用Excel来引用当前未打开的工作簿中的单元格时，就会出现问题.问题:
The problem comes when I try to use Excel to reference cells in workbooks that are not currently open. The problems:
- INDEX可以使用硬编码路径访问封闭的工作簿，但是(据我所知)不能接受单元格范围作为文本.要将单元格范围输入为其他功能的文本，必须使用...
- INDIRECT函数，不适用于封闭的工作簿.
基本上，INDEX可以解决我的问题，但是我不知道如何在不将封闭工作簿的路径硬编码到函数调用中的情况下使其工作.这很麻烦，因为我有成千上万的工作簿可供参考，并且进行查找替换以更改每个工作簿的文件路径既费时又无法维护.
Basically, INDEX can solve my problem but I can't figure out how to get it to work without hard-coding the paths to the closed workbook into the function call. That's a deal breaker, since I have thousands of workbooks to reference and doing a find-replace to change the file path for each workbook is time-prohibitive and not maintainable.
其他限制:没有Excel加载项，因为此工作表必须与其他人共享，也没有VBA，因为担心宏的人必须使用此工作表.我认识到Excel不是适合此工作的合适工具.相信我，如果我可以使用其他工具，我会的.
Other constraints: no Excel add-ins since this sheet has to be shared with others and no VBA because this has to be used by people with fear of macros. I recognize that Excel is not the right tool for this job. Believe me, if I could use another tool, I would.
更新:示例Excel工作表显示了问题:
Update: sample Excel sheet showing the problem:
推荐答案
Going straight to the source at MS Office support, INDIRECT does not work with external workbooks.
这篇关于Excel公式引用具有动态文件路径的另一个(封闭式)工作簿中的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
