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

OK - I didn't express properly - I tried to use an abreviated set. Here is the actual:
in the formula bar when I use mouse to select between open workbooks:
='C:\Users\mettlerg\Desktop\eMRBM\RTP\Dec\[merge 12_11_12.arp Calc to - ML9822D8899 eMRBM 1 & 2 Ship to FSA.xls]Planner_Summary'!AS8
this renders the correct value in E4

In A1 I have: 'C:\Users\mettlerg\Desktop\eMRBM\RTP\ (the basic path)
in A4 I have Dec (the month)
In B4 I have [merge 12_11_12.arp Calc to - ML9822D8899 eMRBM 1 & 2 Ship to FSA.xls] (the file name)
In C4 I have Planner_Summary' (the workbook tab name)
I want a formula that I can concatenate that will give the correct value when I enter the file name and month (I only need to change the cell reference - !AS9, !AS10, !AS11 etc.)

when I do this, the formula looks like: =A1&A4&B4&C4!AS8
what I get is: =A1&A4&B4&C4!AS8 instead of the value
if I use Indirect() as suggested, I get: =A1&A4&INDIRECT(B4)&C4&!AS8
no errors but no values either

there must be a way to do this

regards,
Glen