Formula Referencing SharePoint File Will Not Work Unless File is Open

[Aimee S.]

Hello.

So I have this formula in place that I put together that does exactly what I need it to do. If the source file on SharePoint is open in the same instance of Excel as the file with the formula, it works perfectly. Otherwise, I get a bunch of #VALUE! cells. I have the same security settings for the sharepoint file that has the formula as the sharepoint file it is looking at, both are the same type of excel files, and both on the same sharepoint page. Please advise.

=IF(SUMIFS('https://tel.intranet.teldta.com/ns/programs/Ops Review/[Consultants.xlsm]Consultants'!$Q:$Q,'https://tel.intranet.teldta.com/ns/programs/Ops Review/[Consultants.xlsm]Consultants'!$I:$I,"=Stimulus",'https://tel.intranet.teldta.com/ns/programs/Ops Review/[Consultants.xlsm]Consultants'!$R:$R,">="&$AF$3,'https://tel.intranet.teldta.com/ns/programs/Ops Review/[Consultants.xlsm]Consultants'!$R:$R,"<="&$AG$3)>0,SUMIFS('https://tel.intranet.teldta.com/ns/programs/Ops Review/[Consultants.xlsm]Consultants'!$Q:$Q,'https://tel.intranet.teldta.com/ns/programs/Ops Review/[Consultants.xlsm]Consultants'!$I:$I,"=Stimulus",'https://tel.intranet.teldta.com/ns/programs/Ops Review/[Consultants.xlsm]Consultants'!$R:$R,">="&$AF$3,'https://tel.intranet.teldta.com/ns/programs/Ops Review/[Consultants.xlsm]Consultants'!$R:$R,"<="&$AG$3),NA())