+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    05-08-2008
    Posts
    35

    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
    Last edited by mattydalton; 12-02-2008 at 11:36 AM.

  2. #2
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,062
    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.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,554
    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.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,062
    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.

  5. #5
    Registered User
    Join Date
    05-08-2008
    Posts
    35
    I actually worked this out for anyone who gets this error:

    The maximum number of rows that can be calculated from an external data source is around 16000 - I had a sumproduct that looked at 15000 rows for the first criteria, 15000 rows for the second criteria and returned the sum of another 15000 rows.

    In total, there were 45000 rows as Excel saw it, although they were all from the same 15000 row selection! I amended the formula to 5000 for each and it worked fine. I have subsequently attempted a new approach whereby I summarise the data so that the calculations are minimised and have hyperlinks to specific files to query the data further

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1