external link reference formula inside formula user input

Dear Excel Forum

I am trying the write a formula within a formula, from linking data from another worksheet, using the date as a reference

ie.

in worksheet- file a 01 jan 2012.xls
a1 = 100

in worksheet- file a 02 jan 2012.xls
a1 = 200

in worksheet- file a 03 feb 2012.xls
a1 = 300

in worksheet- file a 04 feb 2012.xls
a1 = 400


in worksheet- link
a1 ='c:\[file a 01 jan 2012.xls]sheet1'!$a$1 ,,, how to edit the file name as user input = 01 jan 2012 or 02
jan 2012 ...
=100


or

b1 = c:\
c1 = file a
d1 = 04 jan 2012
e1 = .xls


a1 = concatenate b1 + c1 + d1 + e1 ,,, how to create this formula
= 400

Thank you.

You need INDIRECT()
http://www.contextures.com/xlFunctions05.html
look at:

Create a Reference To a Different Workbook

Dear dewilk

Thank you for your reply.

But the problem is that the workbook[s] are closed.
Is there any way round this, as I have to create links to more than 10 files (i could do a open.worksheet in the vba code, but this would open many workbooks)

Dear excel forum,

i have found the links to my answer
its ... indirect() , pull()