External reference inside function INDIRECT

[jlonn]

I have a workbook with data in 84 separate sheets, all of which have the same format. The data sheets represent different projects. The data I am interested in, a type of expenditure, is in column D, rows 22 – 61 of the sheets. The corresponding row in column B contains an identifier which indicates what supplier the expenditure in that particular row was charged on.</SPAN>

I have introduced an 85^th</SPAN> sheet, in which I calculate grand totals over the 84 sheets for each supplier. Sums like this one:</SPAN>

=SUMPRODUCT(SUMIF(INDIRECT("'" & $A$37:$A$120 & "'!B22:B61"),$A6,INDIRECT("'" & $A$37:$A$120 & "'!D22:D61")))</SPAN>

Here, cell A6 contains the supplier identifier for which the sum is calculated. Cells A37 – A120 contain the names of the 84 data sheets over which the sum is calculated.</SPAN>

I would like to put the summary table with the grand totals in a separate workbook. Assuming that the data sheets are in a file called C:\Users\Me\data.xls what would the formula above look like?</SPAN>

The ranges B22:B61 and D22:D61 would now be in a different file that would have to be referenced to appropriately. I have tried to put in the external reference but I do not seem to get it right. Can anyone help?</SPAN>

I am using Excel 2010 on Windows 7.</SPAN>

 

[Dave Patton]

Assumptions
1. both files are in the same folder
2. both files are open


N.B.
a. I named the list of sheets SheetList.

Formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&"[Data.xls]"&SheetList&"'!B22:B61"),B6,INDIRECT("'"&"[Data.xls]"&SheetList&"'!D22:D61")))

b. You could include the File name in the SheetList. The formula would then be as follows

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!B22:B61"),B6,INDIRECT("'"&SheetList&"'!D22:D61")))

 

[jlonn]

Thank you so much! I have played around with this all day, but I still get a #REF! error with the formula as you suggest. I do not see why this should happen, but it still happens. The only time I do not get a #REF! error is if I put the formula in the cell between the two INDIRECT expressions (B6 in your example above), but this is the cell supposed to contain the supplier identifier I want to sum for, not the formula. And I get a circular reference warning and a result of 0 instead. Any thoughts on this?</SPAN>

 

[Dave Patton]

1.
When you get the ref error, are both files open?


Both files must be open, see Microsoft's help for Indirect.

2. Both suggestions build the same formula formula; consequently, they should both work.

3. The criteria in B6 is on the reporting sheet not the data workbook.

Your comment "The only time I do not get a #REF! error is if I put the formula in the cell between the two INDIRECT expressions " is not clear to me.

I recommend the you try the formulas with a very concise example (just a couple of rows of data) and then review the formula with Formula Evaluation.

 

[jlonn]

Yes, both files are open.</SPAN>

I understand B6 is on the reporting sheet. It is meant to contain an identifier like “SUPPLIER1”, which the formula then looks for in cells B22:B61 in the sheets of the data workbook. (I actually had the criterion in cell A6 rather than B6 in my original example, but it does not matter.) What I tried to say in the unclear comment is that if I paste the formula into the cell with the supplier criterion (B6 in your formula) and thus overwrite the criterion entirely, the #REF! error goes away and I am left with a circular reference warning. Not a very important point, just something I noticed by accident, when I pasted the formula into the wrong cell.</SPAN>

I am building a very concise example from scratch now, but I do not see why the formula you suggest should not work with original files as it is.</SPAN>

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F
1					1b	[Data.xls]1b
2	SUPPLIER1				1c	[Data.xls]1c
3
4
5
6	SUPPLIER1			108	108
1a

Excel 2003

Cell Formulas
Range	Formula
A2	='[Data.xls]1b'!$B$22
D6	=SUMPRODUCT(SUMIF(INDIRECT("'"&"[Data.xls]"&SheetList&"'!B22:B61"),A6,INDIRECT("'"&"[Data.xls]"&SheetList&"'!D22:D61")))
E6	=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetlist2&"'!B22:B61"),A6,INDIRECT("'"&Sheetlist2&"'!D22:D61")))

Excel Workbook
Name	Refers To
SheetList	='1a'!$E$1:$E$2
Sheetlist2	='1a'!$F$1:$F$2
Workbook Defined Names

The above shows the 2 alternatives cited above. I used A6 as the criteria cell.

 

[jlonn]

Thank you so much again! For some reason this does not work for me. I have constructed the exact same table as yours above and simple sheets 1b and 1c in a separate file called Data.xls, but no, I get the same errors as before. Did you construct the actual files to try this out? </SPAN>

 

[Dave Patton]

Did you construct the actual files to try this out? Yes

The information printed came direct from a spreadsheet.

Please try Excel's Formula Evaluation and determine what part of the formula is not working for you.

 

[Dave Patton]

Did you construct the actual files to try this out? Yes

The information printed came direct from a spreadsheet.

Please try Excel's Formula Evaluation and determine what part of the formula is not working for you.

If you named the range with the Sheet names, please ensure that the name includes the proper sheet name.
The sheet that I posted above is sheet "1a"; the range that includes the sheet names is on sheet 1a.

You can send a PM to me with your email address and I will send you the 2 spreadsheets.

System timed out before I sent this message.

Dave

 

[jlonn]

Interestingly, when I open my files on a different computer (Citrix server), the calculation works perfectly. I run Excel 2010 on both computers. On the desktop computer, I get a #REF! error upon the evaluation of the range SheetList / SheetList2. I go through the exact same sequence of steps on the server computer with no error.