Multiple date based SUMPRODUCT failing

It depends on how much data you're adding - ie how much excess capacity you would need to add in to your fixed ranges to account for future data... if you're talking a few hundred rows of excess then it won't make a huge difference IMO and there are overheads with Dynamic Named Ranges anyway so the cost will to some extent be offset by their removal.

As for alternatives... well I'm afraid I'm not a fan of external links ... I'm a firm believer in keeping files independent from one another in a "live" sense.
If you need data from file 2 for calculations in file 1 it is IMO best to store it in file 1, if that means copy/paste from one file to another so be it - that process can always be automated via VBA (Mac 2008 excepted!).

Others argue the above is risky given you're not necessarily working with "latest data" etc and as such prefer to link but as you've discovered that brings with it it's own limitations. Others use ADO, external data queries etc

I've always believed in being able to save a network file locally, hop onto a plane and work on the file seamlessly before uploading said file back to the network upon arrival... linked files generally speaking (and I say generally) prevent that kind of thing or at best make it a "faff".