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...

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

[JBeaucaire]

First, the " " before the workbook reference needs to include the apostrophe, and it appears again AFTER the sheetname.

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

Indirectly... that becomes

=SUMPRODUCT(--(INDIRECT("'[" & D2 & "]" & E2 & "'!$B$10:$B$148)=G$10)....



NOT: Keep in mind that INDIRECT() formulas will not pull information back from closed workbooks.

See here for a workaround: INDIRECT.EXT()

[genrentinc]

Formula is still kicking back an error message. I had all linked workbooks opened. I uploaded the two workbooks to see exactly what I am trying to accomplish...

[daddylonglegs]

Try this formula in G12

=SUMPRODUCT(--(INDIRECT("'["&D12&"]"&E12&"'!B10:B148")=G$10),INDIRECT("'["&D12&"]"&E12&"'!E10:E148"))

[genrentinc]

daddylonglegs:

That formula works great. Thank you for the help it is greatly appreciated.