Dynamic workbook reference within Sumproduct to retrieve data from closed workbooks with tab names that change based upon the requested date of a user

[Ryan in Trenton]

Hi. This is my first post. I hope this is sufficiently explanatory.

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 : )

 

[Ryan in Trenton]

Tinkering, concerned my syntax is faulty, I created another reference cell (M22) to concatenate the file reference prior to the range (in other words, the drive, folder, & workbook). This also works, but, again, not when the workbook is closed:

=SUMPRODUCT(--(INDIRECT("'"&$M$22&"$A$4:$A$100")=$B$25),--(INDIRECT("'"&$M$22&"$B$4:B$100")=$B27),--(INDIRECT("'"&$M$22&"$L$4:$L$100")))

where $M$22 is a formula: =$C$23&$E$25
where $C$23 is the file extension: C:\FolderName\[Workbook.xls] ( in my example, it's M:\2017 Fab-Spec-66 Saw Performance\[2017 66 Saw Daily Production.xls] )
where $E$25 is the concatenated sheet reference, plus the apostrophe and the exclamation point: Sheet'! ( in my example, it's JAN'! - or whatever month code the input date generates w/ the text formula )

This works, but only when the workbook is open. That means, given the structure above, Excel can return the correct figure. First time I've had an issue with SUMPRODUCT not being able to calculate when the workbook is closed, though. Moved to that formula after troubleshooting past SUMIFS, which is great, but apparently won't work on closed workbooks.

If I should post a copy of my workbook (though I cannot post the source data), please let me know.

Thanks for your thoughts : )

 

[Eric W]

Welcome to the forum.

You've pretty much found the issue. INDIRECT does not work on closed workbooks. If you Google around a bit, you can find various workarounds, some involving VBA, or the Evaluate macro, or even more arcane methods. Given what I've seen from your description, you could create a column of formulas somewhere, one for each month. Then when the user selects a month, you use an INDEX to get the right value from the list. If you have multiple formulas pointing to that workbook, you could create a special sheet on the open workbook, using INDEX to pull over all the values from all the sheets from the closed workbook. Then the formulas could reference that sheet (which you'd probably want to hide).

As I said, not a lot of good options. Good luck.

 

[Ryan in Trenton]

Eric, thank you. I greatly appreciate your insight. That's a brilliant suggestion. I had not thought of it, but it sounds like using INDEX in a new sheet would get around the issue of the data being in a closed workbook, and that will serve my purposes, here, perfectly. I'll try looking up VBA, as I've heard there's a PULL function that may be useful. Once I get this to work, I'll shoot you a confirmation with another 'thank you' for the sensible solution. Having an option that I can apply now, even if I continue to fiddle to improve my understanding, is tremendously relieving. Thanks (so much) for your time!

 

[Eric W]

Glad to help! Let us know if you find something interesting.