Sum dynamic range in closed workbook using address/match

Hello all,

I am trying to sum a variable date range, but the data is in another file and I don't want to open it every time. I noticed when I use =SUM('[file folder]\[file name]Worksheet'!$A$1:'[file folder]\[file name]Worksheet'!$A$3), it returns an error, but if I use =SUM'[file folder]\[file name]Worksheet'!$A$1:$A$3) it would sum correctly. I assume Excel cannot handle two file paths in a range, despite that they are the same file path.

I hoped that if I can find out the starting and ending cell addresses of the range, I may be able to find a solution. I used =ADDRESS(MATCH) to identify the starting and ending cell addresses, and I know the file path, so I wrote a formula to put it all together in G1 so that it reads '[file folder]\[file name]Worksheet'!$A$1:$A$3. However, I can't find a way to =SUM(G1).

I've read in other forums that it's impossible to sum a dynamic range, but I haven't found anyone trying to use the actual cell addresses to hard code the range. Any help is appreciated. Thanks a bunch!

-J

Thanks FDibbins,

I agree that would be the ideal way, but I don't have writing privileges to the "other" workbook. The other workbook is submitted to me and cannot be modified. Only if it were that easy!

I get 30 or so of these other workbooks, which I have to extract and compile, which is why I'd rather not open every one every time I need to make a calculation. And the range is specified on my workbook, so I won't always be referencing the same range in the other workbooks.

FYI: Cross post: http://www.excelguru.ca/forums/showt...-address-match

No because there is other information in that column, but thank you Tony.

I think I need to clarify. I receive multiple reports that I cannot edit. Each report has 5 data sets, one stacked on top of the other, with a total row for each data set. I forget which row the total columns are, but they sum everything above it in that particular data set. I've attached an example with just the total rows. At the very top of the data sets is the month for which the data below corresponds.

On a new workbook, I need to sum each of the totals from all of these many workbooks that I receive, but I only need the totals based on a date range that I, or another user, sets. For example, I may need to sum Total 2 for Companies A through Z (Companies A through Z are all on separate workbooks), for the months of Jun 2012 - Oct 2012. Any idea how I can do that when the data workbooks are closed?

I placed a start date and end date on the new workbook and tried using sum with index/match to find the date range in each of the data workbooks, but it returns an error because Excel cannot handle two file paths when summing a range. That's why I've been playing with the index/address function to see if that works. Please feel free to ask me questions if I'm still not clear enough to describe what I'm trying to do. Thanks!

This works perfectly, but only if I keep the workbook open to perform the calculation. I get a #REF! error if the source workbook is closed and I think its because Excel cannot handle two file paths where there's a range in the sum function. I was hoping I could avoid opening the source workbooks since I have so many of them.

You sir, are a Genius! I cannot express thank you enough! Seriously, EPIC work!