根据单元格值数据按路径引用 Excel 工作簿 [英] Refer to Excel workbook by path, based on cell value data

问题描述

我有一个 Excel 工作表，可以从其他已关闭的 Excel 工作簿中提取数据.目前，当我列出关闭的工作簿的整个路径时，它工作正常，但我想使用存储在单独单元格中的变量作为路径名的一部分.

I have an Excel sheet that draws data from other, closed Excel workbooks. Currently it works fine when I list out the closed workbook's entire path, but I'd like to use a variable, stored in a separate cell, as part of the path name.

例如，我试图引用一个名为

For example, I am trying to reference a workbook called

workbook12.10.12.xls

workbook12.10.12.xls

在一个单独的工作簿(我们会说活动"工作簿)中，我有一个带有公式的单元格

In a separate workbook (we'll say the "active" workbook), I have a cell with formula

=INDEX('C:Path[workbook12.10.12.xls]SHEET1'!$B$1:$B$5, MATCH("匹配文本", 'C:Path[workbook12.10.12.xls]SHEET1'!$A$1:$A$5, 0))

=INDEX('C:Path[workbook12.10.12.xls]SHEET1'!$B$1:$B$5, MATCH("match text", 'C:Path[workbook12.10.12.xls]SHEET1'!$A$1:$A$5, 0))

在 workbook12.10.12 的 B 列中查找与包含匹配文本"的 A 列中的单元格对应的值.这很好用；但是，我在活动工作簿中有一个单元格，其值为

which finds the value in workbook12.10.12's B column corresponding to the cell in the A column that contains "match text." This works fine; however, I have a cell in the active workbook with the value

12.10.12

并且想以某种方式在 INDEX 函数中引用这个值.

and would like to somehow reference this value in the INDEX function.

我无法打开其他工作簿，因此 INDIRECT 函数无济于事.谷歌搜索似乎表明 Excel 没有针对此类事情的简单一站式解决方案......有人可以帮忙吗?谢谢！

I can't have the other workbooks open, so the INDIRECT function won't help. Googling seems to suggest that Excel doesn't have a simple one-stop solution for this kind of thing... can someone help please? Thanks!

推荐答案

来自 Frank Kabel 2004 年在 Dicks 博客上的帖子你可以

1. 使用 Laurent Longre 开发了免费加载项 MOREFUNC.XLL，其中包含 INDIRECT 功能.外部
2. 使用 SQL.REQUEST 作为 此处描述 *似乎不再受支持，并且我不清楚这是否可以处理您的 INDEXMATCH 请求
3. 使用 Harlan Grove 的 PULL 功能

1. Use Laurent Longre has developed the free add-in MOREFUNC.XLL which includes the function INDIRECT.EXT
2. Use SQL.REQUEST as described here *does not appear to be supported anymore and I am not clear if this could handle your INDEXMATCH request
3. Use Harlan Grove’s PULL function

此外，您还可以:

1. 直接通过代码创建一个脏链接"，该代码输入一个引用您需要的工作簿的公式
2. 对于提取值 - 但不适用于范围 - 您可以使用 Walkenbach 的 ExecuteExcel4MacroXLM方法

1. Create a "dirty link" directly via code that enters a formula referring to the workbook you need
2. For pulling values - but not for working with ranges - you could use Walkenbach's ExecuteExcel4Macro XLM method

这篇关于根据单元格值数据按路径引用 Excel 工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！