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

use INDIRECT() - inside the brackets you can make the link up ... be sure to insert the 's either side of the workbook and the exclamation mark

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

If you *carefully* compare the path refs you'll see the reason why your
concatenation doesn't work!

Take note (specifically) how the path ref that *does work* formats
construction of string; it begins with an apostrophe, wraps the
filename in square brackets, and closes the ref after the sheetname
with another apostrophe followed by an exclamation character and the
the range ref.

Construct your concatenation so it formats your string exactly the
same. IOW, the constant characters ('[]'!) *must* be arranged around
the variables (path,filename,sheetname) in the same way in front of the
range ref!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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

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