Hi all,

I have a list of monthly totals in multiple closed workbooks and I want to compile that information into a new workbook based on date intervals that I define (ie start/end dates). I tried using index match to where the formula would look like =SUM('file directory\[workbook]sheet'!start date:'file directory\[workbook]sheet'!end date), but this returns an error. PS, the start date and end date correspond to the actual cells (eg A1 and A3) and is not a named range. However, I noticed that if I change the formula to =SUM('file directory\[workbook]sheet'!start date:end date), this returned the correct totals. I suppose Excel cannot have two file directory notations in a function, even though they are the same directory.

I haven't been able to figure out a formula to sum the monthly totals in the way that allows a user to define the range. The closest I got was using the index/address function to return the actual cell references, and since I know the file path, I was able to create a text string, in say cell G1, that equals ('file directory\[workbook]sheet'!start date:end date), but I cannot figure out how I can use this to =SUM(G1).

Any help is appreciated! Thanks!

Excel 2010
Cross post: excelforum.com/excel-formulas-and-functions/946128-sum-dynamic-range-in-closed-workbook-using-address-match.html