Poor Workbook Performance due to Named Ranges

Hello,

I defined some named ranges in one of my workbooks. I created another
worsheet that uses references to these named ranges to generate counts using
the SUMPRODUCT function. The result has been unacceptably slow performance
in this workbook when using the autofilter to filter by a particular value in
a particular column. I have the same problem when I manually enter a value
or paste a value into a cell and move off the cell. The application says,
"calculating" for about 4-5 minutes.
Is there any way to improve this performance to an acceptable levesl?

Thanks,
John

You should expect a slow down when using array formulas or SUMPRODUCT used in
lieu of an array formula. Each calculation requires multiple -- perhaps 100's
-- of calculations "under the hood".

On Mon, 10 Jan 2005 11:47:07 -0800, jrusso <[email protected]>
wrote:

>Hello,
>
> I defined some named ranges in one of my workbooks. I created another
>worsheet that uses references to these named ranges to generate counts using
>the SUMPRODUCT function. The result has been unacceptably slow performance
>in this workbook when using the autofilter to filter by a particular value in
>a particular column. I have the same problem when I manually enter a value
>or paste a value into a cell and move off the cell. The application says,
>"calculating" for about 4-5 minutes.
> Is there any way to improve this performance to an acceptable levesl?
>
>Thanks,
>John

Just to add to Myrna's post.

Shrink the ranges in your formulas to as small as they can be (but as large as
they have to be).

And in some cases, using a pivottable is an alternative.

jrusso wrote:
>
> Hello,
>
> I defined some named ranges in one of my workbooks. I created another
> worsheet that uses references to these named ranges to generate counts using
> the SUMPRODUCT function. The result has been unacceptably slow performance
> in this workbook when using the autofilter to filter by a particular value in
> a particular column. I have the same problem when I manually enter a value
> or paste a value into a cell and move off the cell. The application says,
> "calculating" for about 4-5 minutes.
> Is there any way to improve this performance to an acceptable levesl?
>
> Thanks,
> John

--

Dave Peterson