Wildcard reference to another workbook with INDIRE

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(""&"Lookup Value"&"",INDIRECT("'["&""&"my workbook"&""&".xlsm"&"]"&"("&A7&")"&"'!$A:$H"),8,FALSE)

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.

I'm definitely doing something wrong.

can anyone help please?

thanks in advance.

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.

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.

Edit: 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.

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.