Excel Error - "Avaliable Resources"

[Guest]

I want to know how to correct for this error message: "Excel cannot complete
this task with available resources. Choose less data or close other
applications."

I am using two spread sheets. with only a small amount of data - 20 Colums
by 2000 rows. In addition to simple SUMIF, VLOOKUP and RIGHT formulas I am
primarily using the DSUM for many calculations . All are giving the correct
return except one that is in the middle of my of the return spread sheet.
This may be the result of the error message. Also, there are no circular
issues going on. This problem persists even though all other windows are
closed.
Hopefully some one can help or possibly direct me as to how to research
this problem further. Thanks Wally

 

[Guest]

The "available resources" message has always come up for me when Excel cannot
find enough computer RAM memory. In fact, some of my workbooks are so large
that you'd be surprised what causes my PC to run out of enough memory. For
example, just when copying a formula from the top of the column to all the
rows beneath it, Excel seems to use memory in case of a possible "Edit/Undo".
After a few such copy/paste operations, my large workbook ends up blowing
out of memory. Not only do I sometimes get the "available resources"
message, but I have had Excel simply quit.

Truly, Wally OZ, the database functions can really be tricky. Typically, I
run into trouble with the criteria. And that's usually what causes the
"return" to get messed up.

Are you sure you cannot avoid the DSUM in favor of using SUMIF? I always
prefer SUMIF. And if the criteria I need to sum upon involve more than one
field (i.e. column) of data, I get around as follows. I make a new column
and use "concatenate" function to join the data from two or more other
columns. Like this:

A B C D
1 Region Product Feature WorkArea
2 East Widget1 FancyLegXYZ =concatenate(A2,".",B2,".",C2)

The result for cell D2 is "East.Widget1.FancyLegXYZ". Using the "." just
serves as a separator of the fields. (You need to make sure it is something
that would never appear in the data. Perhaps a really odd character like
underscore (_).) Now, the criteria in the SUMIF (second parameter) can be
something like this:

=SUMIF(...,"East.*.FancyLegXYZ",...)

In this example, the return is the sum related to East Region of Feature
FancyLegXYZ regards whether Product is Widget1, Widget2, or any other
Product. It works because the asterisk (*) is a wildcard criteria symbol
that means "anything of any length will do", and the separator (in the
example, the period (.)) delimits the portion of WorkArea that is wildcarded.

Hope this offer of a way to work around your problem helps.