using 2007
i need to gather data from various workbooks in different folders within a main folder and summarize the data - example: I have workbooks in main directory - C:\mydata
the subdirectories are by month - Jan, Feb, Mar etc
the Summary workbook - SumData.xls resides in a different folder
SumData is open
then, i open a workbook i need data from say Jan_Data
then in cell C3 i enter = and find the cell in the Jan_Data - say AA15

so far so good
in the formula bar i get - 'C:\mydata\Jan\[Jan_Data.xls]Sheet1'!AA15
and the value in C3 is the value from Jan_Data of sheet1 cell AA15 - say 121
OK
what I want to do is put the basic path - C:\mydata\ - into cell A1
and put the month folder - in this case, Jan, into A3
and put the file name - in this case Jan_Data.xls - into B3

i would then enter the month - say, Feb - into A3
and the file name into B3
and enter a concatenation into C3
the formula would then look something like: =$A$1 & $A3 & $B3 & "Sheet1'!AA15

however, it does not work
i don't get an error but i do get $A$1 & $A3 & $B3 & "Sheet1'!AA15 and not the value

in summary, i want to enter the name of the workbook and have the concatenated formula
pull the data from the appropriate place so i don't have to edit/enter each and every formula every time i enter new data. there are several additional columns of summary data

how can i do this?

regards,

glen