On Saturday, March 16, 2013 7:19:41 AM UTC-5, Glen Mettler wrote: 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