why Sumif, sumifs, or sumproduct formulas not automatically update unless....

[Uzair]

Please help me

why is that Sumif, sumifs, or sumproduct formulas not automatically update unless the source file is opened.

 

[Luke M]

In short, "that's just the way it is". Certain formulas work on external closed workbooks (my guess is it has to do with how much data is being pulled). A workaround is to dedicate one sheet of the "output" workbook to pulling data, using simple formulas like:

=[Otherworkbook.xls]Sheet1!A1


And then run all your SUMIFS, SUMPRODUCTS from that.


For other posters, link to duplicate thread:

http://chandoo.org/forums/topic/links-dont-update-unless-source-file-is-open

 

[Hui]

There is a good article at Daily Dose of Excel on this which is worth a read

http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/


The comments also add other options

 

[Uzair]

Thanks