VLOOKUP formula with file name reference

[patsfan]

Greetings, can someone help me add a variable to a VLOOKUP formula which references the name of a separate file? This formula below is accurate and references the correct file BUT I would like the Customer number in the filename (2597) to change according to the contents in cell A1 of my current spreadsheet.

=VLOOKUP(A5,'jupiterprojectsBusiness AccountsTexasCustomer 2597 Total.xls'!PurchaseData,5,)

 

[Debraj]

Hi patsfan,


try to use INDIRECT..


=VLOOKUP(A5,Indirect("'jupiterprojectsBusiness AccountsTexasCustomer " & A1 &" Total.xls'!PurchaseData",5,False)


Regards,

Deb

 

[patsfan]

Thanks Deb, Just what I needed. I did have to enter a ")" after the last quote. For some reason, the file needs to be open for this to work. Any idea why it won't reference the entire file path?

 

[SirJB7]

Hi, patsfan!

The reason is that INDIRECT function doesn't work on closed workbooks.

Regards!