Formula in Excel that references another Excel file based on cell reference

I want to make a formula that references a cell in another excel file. I have figured that out as开发者_JAVA技巧 below:

='C:\Users\17\Desktop\[JAN-11 2011.xlsx]1'!$H$44

But as the cell L1 in this worksheet is Data cell set to month so it show current month so I want to be able to do something as below:

='C:\Users\17\Desktop\[&L1& 2011.xlsx]1'!$H$44

but this is not working. How do I get this function to work off of the cell L1?

I think you could use Lance Roberts suggestion, but include it in a INDIRECT() function, like this: =INDIRECT('C:\Users\17\Desktop[' & L1 & ' 2011.xlsx]1'!$H$44)

Try using string concatenation like so:

='C:\Users\17\Desktop[' & TEXT(L1, "mmm-dd") & ' 2011.xlsx]1'!$H$44