sumproduct causing memory errors?

[dave]

Has anyone experienced problems while using sumproducts
which cover large arrays, lets say a few thousand lines,
in an exterior workbook?

More specifically, I'm using sumproduct in spreadsheet 1...
=sumproduct(--([array in spdsht 2]=[reference in spdsht
1]),--([same as first ex. for another qualifier]),--([sum
array in spdsht 2]))
Formula works correctly when spdsht2 is open. To my
understanding(and some experience) the sumproduct link
should be maintained with spreadsheets that are closed.

When opening spreadsheet 1(without opening spreadsheet 2),
I receive an error which says, "excel cannot complete this
task with available resources. Choose less data or close
other applications" The file still opens but the
sumproduct result cells show N/A. After opening spdsht 2
the links correct themselves. With smaller ranges this
has not been a problem.

I dont think this has anything to do with other apps. Has
anyone run into these apparent link limits with
sumproduct? If so, are there any known solutions?

thanks much to the excel superpowers,
Dave

PS. With even larger arrays or multiple spreadsheets
which each have this issue, the problems seems to
exponentiate, causing files to open without all their
formatting.


..

 

[Guest]

Dave,

I often have the same problem. The only way around it as far as I know is
to either have the external workbook open, or to click in each cell that has
the array formula, press F2 and then enter. Of course, this may not be
practical if you've got more than a handful of cells with arrays.