Working With If/SumProduct/Match And External Sheet References

[Ripture]

Hello,

I am trying to build a sheet which references various external sheets generated by separate software. I have so far found that Excel can store references to these files and pull data from them dynamically without them needing to be opened. This is the important part here: I don't want to have to open the generated sheets for the referencing sheet to have access to the values.

Initially, I had issues with when trying to use SUMIF to get a total from the external sheet. Excel 2007 will not "reach into" the external sheet with SUMIF unless the sheet is opened simultaneously. However, I found that SUMPRODUCT will. Now, I am trying to construct a filtered list from a master list in the externally generated sheet. Each row in the master list contains all data pertaining to some subject. I need to dynamically build 8 separate lists that sort the master list into it's 8 exclusive categories. I have this functioning using INDEX, SMALL, and IF, however, there is some issue interfering with the formula causing it to only work partially if the external sheet is not opened.

More specifically, the arrays dynamically pull zero, one or two entires then stop until the external sheet is opened. This is strange because if it were simply not working at all, I would have assumed, similar to SUMIF, IF could not dynamically reference external sheets. However, it is pulling a variable amount of entries from the external sheet and I have no idea how to figure out why it is doing this.

Thanks for any help on this.

[Ripture]

Still having trouble with this. It seems to generally pull exactly 2 entries max per array. Quite a strange thing. Like I said, I'd expect it to either work completely or not at all. It's probably going to take someone with extensive knowledge of the inner-workings to understand this behavior.