Vlookup from different Excel files... almost there!!

[canadian86]

Here's my formula:

=VLOOKUP(B1,'C:\Corporate\[Store 1234 - 12-10-2011.xls]Ontario'!$C$5:$D$26,2,FALSE)

The ONLY thing that changes in the above formula is the filename: "Store 1234 - 12-10-2011.xls". Instead of manually updating the filename, is there a way to reference it to a list in Column A?

Thanks a lot!

 

[MrKowz]

Try:

=VLOOKUP(B1,INDIRECT("'C:\Corporate\["&A1&".xls]Ontario'!$C$5:$D$26"),2,FALSE)

Where A1 contains "Store 1234 - 12-10-2011"

 

[canadian86]

Thanks. Unfortunately, that ONLY works when I have the source worksheet open. When I close the worksheet and apply the formula it gives me "#REF!"

Is there any way to make this work without having to open all 50 worksheets?

 

[canadian86]

I think I can make it work using this plug-in that allows me to use indirect function on closed workbooks: http://download.cnet.com/Morefunc/3000-2077_4-10423159.html.

Thanks for ur help.