Excel cannot complete this task with available resources.....

Hi all

I am getting the following error message with one of my files:

Excel cannot complete this task with available resources. Choose less data or close other applications

My file is only about 3mb in size, made up of 17 worksheets. These sheets are calculated by referencing to another file that contains all of the background data. The data file is also about 3mb, made up of 13 worksheets.

There are probably about 2 - 3 thousand formulas in the file in total, ranging including vlookups, sumifs, sumproducts, etc..

When the data was contained within the file there was no problem. I moved each data sheet into a new workbook to trim the size of my file and also stop the incessant calculation and this is when the problem started.

Now, when I open the file and am prompted to update, it will update to about half way and then throw up the error message!

Any help would be greatly appreciated

Matt

Changing the data to an external sheet didn't reduce the amount of calculation required the way you think it did. SUMIF and SUMPRODUCT and VLOOKUP, these are memory intesive operations and they update constantly, even when they don't need to. By adding another sheet, you've made it harder on your system to accomplish these tasks, not easier.

Find out how much memory your computer is designed to hold and install it all, max it out.

Consider putting the data back into one sheet. Look for ways to minimize these calculations.

SUMIF and SUMPRODUCT and VLOOKUP ... and they update constantly, even when they don't need to.

Not so, JB. See http://www.decisionmodels.com/calcsecretsi.htm. In addition to the narrative, there is a workbook there (VolatileFuncs.xls) that demonstrates which functions are volatile. Of course, if a volatile function is nested within, then the entire formula becomes volatile.

Good info, good read. That page makes me think it's easier, not harder, to create a memory hog sheet. I'll have to keep those functions in mind. Thanks for the clarification.