Dynamic VLOOKUP file location

[whiteknight]

I have several Excel files that are identification cards for suppliers (an Excel file for each supplier) and I want to retrieve the data for all suppliers in one Excel file using the Vlookup equation

For example

=VLOOKUP(B1,'E:\New folder\1\[supplier1.xlsx]Sheet1'!B:C,2,FALSE)

I want the file name to be changed automatically based on the name of the supplier, supplier1, based on the “a2” field.

 

[Fluff]

The only way to do that with a formula is to use the INDIRECT function, but it does not work if the other workbook is closed.

 

[whiteknight]

The only way to do that with a formula is to use the INDIRECT function, but it does not work if the other workbook is closed.

thanks
is it possible to use VBA to achieve the same result if the other workbook is closed

 

[Fluff]

The simplest would be for VBA to open the other file.

 

[whiteknight]

The simplest would be for VBA to open the other file.

are there other options because i have hundreds of files

 

[Fluff]

Not that I can think of.