Windows XP / Excel 2007 ->
I have a workbook with 12 tabs. The primary tab is a pipeline of sales data -- most other tabs related to analyses based on this data (pivottables, related charts, historical analysis) or support this data (tab for each data validation set).
When I started this project, I set the pipeline up as an Excel Table to take advantage of those features. Initially I had about 100 records in the table and probably 15 columns. I'm up to about 175 records in the table and about 22 columns. 8 of the columns are subject to data validation -- generally just lists. Records are a mixture of text and numbers -- only one formula. I have a few offset formulas used in range names on this tab.
Up until about 2 weeks ago, this was humming along nicely. It has turned into a super slow memory hog now. It takes 15 seconds to apply or remove a simple filter. Same with entering a two digit number in a field. Forget about paging down or arrowing across. Last night I tried to replace the print area range (removed the offset formula and referenced just the table name). It gave me an out of memory error.
In trying to solve the problem, I have removed as many offset formulas as I could that referred to entire columns or rows to count range size. I had one data validation that used an Indirect formula to limit the data selection based on a response in another column -- I removed that. I'm sort of out of ideas. It's worth noting that performance on every other tab is just fine (a few of the other tabs also contain tables, but much smaller).
Is the Table itself causing the issue (too many records)? Is there some other gremlin that is causing this to bog down? Where could I start looking to diagnose my problem? It is nearly unusable now -- I have no idea how to roll it back to usable. Thanks in advance.
I have a workbook with 12 tabs. The primary tab is a pipeline of sales data -- most other tabs related to analyses based on this data (pivottables, related charts, historical analysis) or support this data (tab for each data validation set).
When I started this project, I set the pipeline up as an Excel Table to take advantage of those features. Initially I had about 100 records in the table and probably 15 columns. I'm up to about 175 records in the table and about 22 columns. 8 of the columns are subject to data validation -- generally just lists. Records are a mixture of text and numbers -- only one formula. I have a few offset formulas used in range names on this tab.
Up until about 2 weeks ago, this was humming along nicely. It has turned into a super slow memory hog now. It takes 15 seconds to apply or remove a simple filter. Same with entering a two digit number in a field. Forget about paging down or arrowing across. Last night I tried to replace the print area range (removed the offset formula and referenced just the table name). It gave me an out of memory error.
In trying to solve the problem, I have removed as many offset formulas as I could that referred to entire columns or rows to count range size. I had one data validation that used an Indirect formula to limit the data selection based on a response in another column -- I removed that. I'm sort of out of ideas. It's worth noting that performance on every other tab is just fine (a few of the other tabs also contain tables, but much smaller).
Is the Table itself causing the issue (too many records)? Is there some other gremlin that is causing this to bog down? Where could I start looking to diagnose my problem? It is nearly unusable now -- I have no idea how to roll it back to usable. Thanks in advance.
