Please help: VLOOKUP external workbook with concatenated file name

[commytarter]

=VLOOKUP(B6,'C:\Documents\Desktop\input files\[=CONCATENATE(TEXT(B6,"mm.dd.yyyy"),".xls")]01.31.2014'!$E$2:$I$7,2,FALSE)

This is the formula I tried and did not succeed.

I'm trying to pull info from external workbooks that are named with dates I have listed in column B of my current workbook.
I thought I could put a concatenate function inside the brackets, where the file name belongs.

I was also hoping to do this for the tab name.

Am I completely off course here? Someone please help me out

 

[awwcraig1]

Did you find an answer to this? I have a similar issue.

 

[commytarter]

Yes, I used the indirect function.

=INDIRECT(CONCATENATE("[",TEXT(Friday_Date,"mm.dd.yyyy"),".xls]Sheet1!F",MATCH(B6,INDIRECT(CONCATENATE("[",TEXT(Friday_Date,"mm.dd.yyyy"),".xls]Sheet1!E:E")),0)))

 

[markmzz]

Maybe this:

=VLOOKUP(B6,INDIRECT("'C:\Documents\Desktop\input files\["&TEXT(B6,"mm.dd.yyyy")&".xls]01.31.2014'!$E$2:$I$7"),2,0)

Markmzz