通配符引用具有INDIRECT函数的另一个工作簿 [英] Wildcard reference to another workbook with INDIRECT Function

问题描述

我想引用另一个工作簿，假设它的名称为"My workbook.xlsm"，在Excel中具有INDIRECT函数.

I want to make a reference to another workbook , let's say its name is "My workbook.xlsm" with the INDIRECT Function in Excel.

但是我的工作簿"文件名之前或之后可以有任何其他字符串/字符.

However "my workbook" file name could have any other string/characters before it or after it.

假设我正在尝试查找查找值"

let's say I'm Trying to lookup "Lookup Value"

= VLOOKUP(""&查找值"&""，INDIRECT('["&""&我的工作簿"&"&".xlsm&"]&"("& A7&)"&'！$ A:$ H")，8，FALSE)

=VLOOKUP(""&"Lookup Value"&"",INDIRECT("'["&""&"my workbook"&""&".xlsm"&"]"&"("&A7&")"&"'!$A:$H"),8,FALSE)

我尝试了上面的公式，当我删除我的工作簿"参考之前和之后的通配符并完全按原样写出工作簿的名称时，该公式可以正常工作，但是当我放置通配符时，该公式将给出REF错误.

I've tried the above formula and when i remove the wildcards before and after "my workbook" reference and write the name of the workbook exactly as it is , the formula works fine, but when i place the wildcards the formula gives a REF Error.

我肯定做错了事.

任何人都可以帮忙吗?

提前谢谢.

推荐答案

必须打开用INDIRECT()引用的工作簿才能进行引用.因此，除非您碰巧在您的第一个工作簿中同时打开了所有可能满足通配符条件的文件，否则这种方法绝非偶然.

A workbook referenced with INDIRECT() must be open for the reference to work. Therefore, this approach is a non-starter from the go, unless you happen to have all files that might potentially meet the wildcard criteria open at the same time with your first workbook.

您可能想看看一个名为MOREFUNC.XLL的免费加载项，它具有一个功能INDIRECT.EXT()，可用于封闭的工作簿.

You may want to have a look at the free add-in called MOREFUNC.XLL, which has a function INDIRECT.EXT() that works with closed workbooks.

您的公式中没有通配符.通配符可以是单个字符的?或任意数量的字符的*.注释中的视频说明了带有工作表名称的INDIRECT的通配符用法.对于您的示例，公式看起来像

there are no wildcards in your formula above. A wildcard can be a ? for a single character or a * for any number of characters. The wildcard usage for INDIRECT with sheet names is explained in the video in your comment. For your example the formula would look like

=VLOOKUP(A2,INDIRECT("'[*my workbook*.xlsx]Sheet1'!$A:$H"),8,0)

不需要将字符串拆分为一系列串联的字符串，但是如果这样做，则没有任何区别.

There is no need to split up a string into a series of concatenated strings, but if you do, it does not make a difference.

尽管如此，您会发现此公式始终会返回错误.间接不能解析文件名中的通配符.它适用于工作表名称.

You will find that this formula will always return an error, though. Indirect cannot parse wildcards in file names. It works fine with sheet names.

正如我在评论中所说:由于必须一直打开带有查找表的文件，因此INDIRECT()可以首先使用，因此可以使用常规Vlookup并直接引用我的工作簿".如果重命名该文件，则Vlookup公式将立即反映更改.

As I said in my comment: Since the file with the lookup table needs to be open anyway, so INDIRECT() can work in the first place, you can use a regular Vlookup with a direct reference to "my workbook". If you rename that file, the Vlookup formula will reflect the change immediately.

这篇关于通配符引用具有INDIRECT函数的另一个工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！