#### Ryan in Trenton

##### New Member

- Joined
- Sep 7, 2017

- Messages
- 3

I'm trying to pull numbers from a closed workbook. That workbook contains twelve tabs, each containing a separate month's data (which I cannot alter). I use the following formula, and it returns the data as I expect:

{=SUMPRODUCT(--('C:\FolderName\[Book1.xls]Sheet1'!Range1=Criteria1),--('C:\FolderName\[Book1.xls]Sheet1'!Range2=Criteria2),--('C:\FolderName\[Book1.xls]Sheet1'!SumRange))}

Here's the same formula, as it is actually written, which accurately returns the desired data from the closed workbook:

{=SUMPRODUCT(--('M:\2017 Fab-Spec-66Saw Performance\[2017 66 Saw Daily Production.xls]JAN'!$A$1:$A$330=$B$25),--('M:\2017 Fab-Spec-66Saw Performance\[2017 66 Saw Daily Production.xls]JAN'!$B$1:B$330=$B27),--('M:\2017 Fab-Spec-66Saw Performance\[2017 66 Saw Daily Production.xls]JAN'!$L$1:$L$330))}

Now, that's all well & good, but I would also like to make the Sheet ("JAN" in the example immediately above) reference within the formula dynamic, so as to allow for a change in the month's data being sought.

The spreadsheet is set up in such a way as to permit a user to enter a date in a cell B25, from which the appropriate Sheet name is generated (ex - user enters 3/26/17; formula returns "MAR" using a simple TEXT function & TRIM (all the sheet names in the closed workbook are the 1st 3 digits of the month name)) in a cell D25, which I then intend to use as a reference cell for my SUMPRODUCT formula. For additional reference, I entered the entire file location & file name (up to and including the brackets [ ] around the workbook name) in a cell C23, as well. Why not, right?

As you can tell, I need to match two criteria (date & product#) to two ranges in the closed workbook. The date will be entered by the user. From that cell, my spreadsheet pulls data from above (which is a consolidation of data from other workbooks), and populates the product ran that day to the column to the left. I intend to use both the date & that product number as criteria to pull a series of different figures from the closed workbook. As I said, this works just fine in the formula above - until I try to make the Sheet reference dynamic by the following augmentation.

Col. A Col. B Col. C Col. D

Row 23 File Extension Ref

Row 25 Input Date Trimmed Sheet Ref ("JAN")

Row27 Item #1 11005 Formula (where 2nd criteria is $B27)

Row28 Item #2 11218 Formula (where 2nd criteria is $B28)

Row29 Item #3 11294 Formula (where 2nd criteria is $B29)

I would like to think that I could insert an INDIRECT function to assemble the formula given that the Sheet name generated in cell D25 may change. My understanding is that it would look as follows:

{=SUMPRODUCT(--(INDIRECT($C$23&$D$25&"'!"&"$A$1:$A$330")=$B$25)*--(INDIRECT($C$23&$D$25&"'!"&"$B$1:$B$330)=$B27)*--(INDIRECT($C$23&$D$25&"'!"&"$L$1:$L$330"))}

where $C$23 is the reference cell containing the full file path through the brackets [ ] surrounding the workbook name; $D$25 is the Sheet name that changes based upon the date entered by the user in cell $B$25; the reference to $B27 is the product number, and is unlocked so as to allow me to drag this down additional rows (where the B column contains a new, additional product# run on that date); and where I use the -- syntax with SUMPRODUCT to avoid issues with zeroes & blank spaces in the data.

Unfortunately, this generates an #REF ! error. I generally despise INDIRECT (not that I can even get it to work, in this case) but I'm failing to grasp an alternative, either. To prove to myself that this INDIRECT function was not the (sole) issue (as far as I can tell, from my limited understanding), I rewrote the formula as follows. This returns data accurately, but only when the workbook is open, which is not an efficient option for me; when the workbook is closed, it returns an #REF ! error:

{=SUMPRODUCT(SUMIFS(INDIRECT("'"&C23&D25&"'!"&"$L$1:$L$200"),INDIRECT("'"&C23&D25&"'!"&"$A$1:$A$200"),$B$25,INDIRECT("'"&C23&D25&"'!"&"$B$1:$B$200"),$B27))}

So, I presume one of two things is happening (or both): 1) I have my syntax wrong somewhere in the SUMPRODUCT that has the INDIRECT nestled inside (or, it's not possible to imbed that in such a way as I did); or 2) I've lost my mind as I've unceremoniously slipped into the 7th circle of Excel hell by trying to nestle INDIRECT functions inside the SUMPRODUCT function, when I could have saved myself the break from reality by employing some kind of INDEX & MATCH combination (of which, I am, sadly, not very familiar).

I'd greatly appreciate any corrections to the above, or suggestions anyone may have : )