SUMPRODUCT Not working with external source?

Question

Hi

I am using this formula in Spreadsheet Daily.xls

=(SUMPRODUCT(('Y:\Forecast[Diary.xls]Cash'!$C$1:$C$65536="Deposit")*('Y:\Forecast[Diary.xls]Cash'!$B$1:$B$65536=$A9)*('Y:\Forecast[Diary.xls]Cash'!$D$1:$D$65536="GBP"),('Y:\Forecast[Diary.xls]Cash'!$F$1:$F$65536))

It will only work when the source file (Diary.xls) is open. Any ideas how to fix this? I thought sumproduct could be used without the source file being open?

Thanks

J

Answer 1

Hi,

Does it work if you reduce the row number 65536 to a lesser row number, say 50000?

Answer 2

Hi,

This still isn't working. Only will update when I open the source file.

Thanks

Answer 3

If I press F9 after taking F2, it gives #REF..

If I press Enter after taking F2, it recalculates..

See if you pressed Enter....(Also, just double check it is working with open file)

Answer 4

Hi thanks for the reply, I did as you suggested and pressed F2. It comes up as 'unable to read file' then shows a #REF! error.

ANy ideas?

Thanks

Answer 5

SUMPRODUCT will work with closed workbooks.

You will have recalculate that formula..

Take F2 in that cell and Press Enter, it will display the new value.