使用动态文件路径引用另一个(关闭)工作簿中的单元格的 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 等文本函数为所有这些其他工作簿构建文件名.

当我尝试使用 Excel 引用当前未打开的工作簿中的单元格时，问题就出现了.问题:

1. INDEX 可以使用硬编码路径访问封闭的工作簿，但不能(据我所知)接受单元格范围作为文本.要将单元格范围作为文本输入到其他函数中，必须使用...
2. INDIRECT 函数，不适用于关闭的工作簿.

基本上，INDEX 可以解决我的问题，但如果不将封闭工作簿的路径硬编码到函数调用中，我无法弄清楚如何让它工作.这是一个交易破坏者，因为我有数以千计的工作簿可供参考，并且执行查找替换来更改每个工作簿的文件路径是时间限制且不可维护的.

其他限制:没有 Excel 加载项，因为此工作表必须与他人共享，也没有 VBA，因为害怕宏的人必须使用它.我认识到 Excel 不是这项工作的正确工具.相信我，如果我可以使用其他工具，我会的.

更新:显示问题的示例 Excel 表:

解决方案

直接进入

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.

The problem comes when I try to use Excel to reference cells in workbooks that are not currently open. The problems:

1. INDEX can access closed workbooks using hard-coded paths, but can't (as far as I can tell) accept cell ranges as text. To enter cell ranges as text to other functions, one has to use the...
2. INDIRECT function, which doesn't work for closed workbooks.

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.

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.

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屋！