I'm starting a new thread, as I'm not sure if people have seen my reply in the old thread - please let me know if this is a problem!

New day - new problem!

My manager has now asked for a SUMIF summary to be on a separate spreadsheet (so that she can look at it along with other summaries)

Unfortunately, using this formula:
=SUMIF('S:\Internal\Sales Figures\2006-2007\[ES 06-07 Spreadsheet.xls]Activity'!$A$3:$A$1001,1,'S:\Internal\Sales Figures\2006-2007\[ES 06-07 Spreadsheet.xls]Activity'!$C$3:$C$1127)

just brings up a #Value! unless the spreadsheet it is linking to is open. If the linked to spreadsheet is opened, then closed, then I get the figures untill I close the summary spreadsheet. Then, when I reopen the summary spreadsheet, and click on Update (which I would need to if any of the other sections that this spreadsheet links to had been updated), I get #Value! again.

I can't see why this doesn't work when the more simple formulae (eg a straight ='S:\Internal\Sales Figures\2006-2007\[ES 06-07 Spreadsheet.xls]Summary'!$B$21) do. What am I doing wrong?