Sumproduct and Indirect

[genrentinc]

Hello. I am a new member of this forum. I have read through this forum countless times in the past when I have had a problem/question with a formula and I have always seemed to find some sort of help to get me in the right direction. Well I am stumped - I have been working on this for a few days and I can not seem to figure it out... And to be completely honest I do not fully understand how to use the INDIRECT formula - I understand the concept/idea - I have a hard time with the " ' and the &'s of it.

My data is in one workbook with four worksheets. Workbook name is: 2010 Financials.xlsx and worksheet one name is: BS - JAN 2011 - ALL.
Workbook = one fiscal year. Worksheets = each month gets four. I would like to summarize the data from the worksheets in a different workbook (Financial Summary.xlsx). Problem is each month gets four new worksheets and every year I start a new workbook - so I would like to use the INDIRECT formula in my summary workbook to lookup or - in my case - sumproduct the results. My sum product formula works properly when I put the workbook name and worksheet name in...

=SUMPRODUCT(--('[2010 Financials.xlsx]BS - JAN 2011 - ALL'!$B$10:$B$148=G$10),'[2010 Financials.xlsx]BS - JAN 2011 - ALL'!$E$10:$E$148)

On my summary workbook - I have two columns... one (D12 on down) for the file name of the workbook I want the data pulled from (i.e. 2010 Financials.xlsx) and the other column (E12 on down) being the worksheet (i.e. BS - JAN 2011 - ALL) from the workbook I want data pulled from.

I have tried the following with no luck:

=SUMPRODUCT(--((INDIRECT(""&D12&"!]!BS - JAN 2011 - ALL!....

=SUMPRODUCT((INDIRECT("'["&D12&"]!BS - JAN 2011 - ALL!...

Ideally, I would like to have the formula use the file name in D and sheet name in E...

I have also looked at the CONCATENATE formula and I don't think I need it in this instance but I'm not 100% sure of that.

Any help or suggestions to get me in the right direction would be greatly appreciated.

- Chris