SUMIFS function linking to separate worksheet populates #VALUE! when file is closed

[iglooman]

Hi,

I've been struggling with this problem for a few days now, and am getting a little frustrated!

I'm using a SUMIFS function that grabs values from a different Excel worksheet on the server between a given date range. When the Excel workbook is opened, the values populate perfectly. However, when I close the workbook, all numbers using this formula populate as #VALUE! it is opened and again updated.

The code is

=SUMIFS('S:\Section\Finance\Cash\2014\Countries\Australasia\[AUS-Cash-Flow.xlsm]Daily LCY'!$F14:$NF14,'S:\Section\Finance\Cash\2014\Countries\Australasia\[AUS-Cash-Flow.xlsm]Daily LCY'!$F$10:$NF$10,">="&H$5,'S:\Section\Finance\Cash\2014\Countries\Australasia\[AUS-Cash-Flow.xlsm]Daily LCY'!$F$10:$NF$10,"<="&H$6)

Any ideas on what could be causing this problem?

The only lead I've had so far is character length, which is higher than 255, however I'm using Excel 2007, and have saved it as .xlsx and .xlsm with no success.

Thanks for the help.

 

[RoryA]

SUMIFS doesn't work with closed workbooks. You'll need to use SUMPRODUCT instead.

=SUMPRODUCT('S:\Section\Finance\Cash\2014\Countries\Australasia\[AUS-Cash-Flow.xlsm]Daily LCY'!$F14:$NF14,('S:\Section\Finance\Cash\2014\Countries\Australasia\[AUS-Cash-Flow.xlsm]Daily LCY'!$F$10:$NF$10>=H$5)*('S:\Section\Finance\Cash\2014\Countries\Australasia\[AUS-Cash-Flow.xlsm]Daily LCY'!$F$10:$NF$10<=H$6))

 

[iglooman]

Awesome! Works! Thanks so much Rory.
Cheers

 

[RoryA]

Glad to help. Welcome to the forum, by the way!