Dynamic Cell Reference from Sheet on SharePoint

[Tidr0001]

I have weekly historical sales information on a SharePoint website.

ie:
Results 2014.04.04.xlsm
Results 2014.04.11.xlsm
Results 2014.04.18.xlsm

I am creating a summary workbook where I would like to access sales data from each on the sheet stored on SharePoint.

Additionally, I would like to use a SUMIF to tailor the results. The formula needs to be dynamic as the format of each historical report might differ.
For Example:
D10: Defines the Region to look up. Regions are in separate Columns in the data.
D9: Defines the product to look up
E15: Defines the Column holding the sales data that needs to be summed


Had success if pulling info local from the same worksheet.
=SUMIF((INDIRECT(CONCATENATE("Summary!",(LEFT(ADDRESS(1,MATCH($D$10,Summary!$1:$1,0),4),1)),":",(LEFT(ADDRESS(1,MATCH($D$10,Summary!$1:$1,0),4),1))))),$D$9,INDEX(Summary!$1:$1048576,0,MATCH($E15,Summary!$1:$1,0)))

Where:
INDIRECT(CONCATENATE("Summary!",(LEFT(ADDRESS(1,MATCH($D$10,Summary!$1:$1,0),4),1)),":",(LEFT(ADDRESS(1,MATCH($D$10,Summary!$1:$1,0),4),1)))
Determines the "Asia" Products are Stored in Column B:B

Then:

SUMIF(Summary!$B:$B,"Product A",Sales Data in Column E:E)

End

Need something that looks like this (Thing the INDIRECT is breaking it):

=SUMIF((INDIRECT(CONCATENATE("'https://test.com/[Results 2014.04.04.xlsm]Summary'!",(LEFT(ADDRESS(1,MATCH($D$10,'https://test.com/[Results 2014.04.04.xlsm]Summary'!$1:$1,0),4),1)),":",(LEFT(ADDRESS(1,MATCH($D$10,'https://test.com/[Results 2014.04.04.xlsm]Summary'!$1:$1,0),4),1))))),$D$9,INDEX('https://test.com/[Results 2014.04.04.xlsm]Summary'!$1:$1048576,0,MATCH($E15,'https://test.com/[Results 2014.04.04.xlsm]Summary'!$1:$1,0)))