Hi

Some formulas (for sure INDIRECT, and it seems SUMIF too, don't work with
links to closed files). A workaround is mirroring the source data on
separate (hidden) sheet, and referring in formulas to this sheet.

Example:
Create a sheet Aug
Into cell A1 on sheet Aug, enter the formula
=IF('D:\temp\[123.xls]Aug'!A1="","",'D:\temp\[123.xls]Aug'!A1)
Copy the cell A1, and paste to whole column A (Or to some reasonable range.
And you can mirror other columns of your source table too - all of them or
selected ones. Once the formula is copied, you can reorder columns at will,
using drag-and-drop)
(Hide the sheet Aug, when you don't want it to be visible)

Change your formula to
=SUMIF(Aug!$A:$A,AS!C9,Aug!$G:$G)

Arvi Laanemets


"Simon" wrote in message
...
I've tried using SUMIF with linked sheets and it has some problems.
i.e.
=SUMIF('D:\temp\[123.xls]Aug'!$A:$A,AS!C9,'D:\temp\[123.xls]Aug'!$G:$G)

The cells returns #VALUE.
But when I open the linked sheet (123.xls), the formula works fine.

Does anyone have any workarounds ?

Thanks,