SUMProduct Formulas

[Toastis]

I have a sum product formula which is connected to a data source that is not located in the same workbook. It causes a long lag to pull it up or make any modifications to that page. However, saving the entire document is not burdened by it. What can I do to still retrieve the data without the bogging down of the formula?

=SUMPRODUCT(('C:\Application Data\Microsoft\Excel\KPI Data\[Main KPI Data 2011.xlsm]Main KPI Data Sheet'!$E:$E=A543)*('C:\Application Data\Microsoft\Excel\KPI Data\[Main KPI Data 2011.xlsm]Main KPI Data Sheet'!$AB:$AB=$P$1))

It usually says something about Calculate, Processor 4 10%... when it's trying to access it. Sometimes will tell me that my links are broken and if I tell it to proceed without updating it opens fine, once I need to update those cells.. it bogs down again.

Hope it makes sense.

 

[njimack]

It would probably calculate a lot faster if you had the source workbook open. Also, do you really need to refer to entire columns (i.e 1 million rows)?

 

[Toastis]

It would probably calculate a lot faster if you had the source workbook open. Also, do you really need to refer to entire columns (i.e 1 million rows)?

Not if you can tell me how to ensure that it always looks at the updated rows when I add new data?

 

[njimack]

Not if you can tell me how to ensure that it always looks at the updated rows when I add new data?

Take a look at dynamic named ranges - http://www.contextures.com/xlNames01.html#Dynamic