Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically increase Named Ranges | Excel Discussion (Misc queries) | |||
How to Link named ranges from multiple Workbooks into a single Wo. | Excel Discussion (Misc queries) | |||
Named Cell Ranges | Excel Discussion (Misc queries) | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |