Minor Change Caused Major Increase in Calculation and Saving Time (1 Viewer)

L

lemo

Registered User.
Local time
Yesterday, 21:06
Joined
Apr 30, 2008
Messages
187
another day another excel quirk!
this is excel 2007, on xp.

i made a few relatively innocuous changes -
- added 1 variable to a pivot table (have 3 now) and rearranged them a bit,
- eliminated about a 1000 rows of calculations, that used light formulas (double-nested IFs) and conditional formatting,
- applied similar conditional formatting to the pivot table,
- moved block of calculations to a new location.

now it takes FOREVER to change the pivot table filter AND to save file.

yours at loss,
l
 
S

shades

Registered User.
Local time
Yesterday, 20:06
Joined
Mar 25, 2002
Messages
516
Howdy. :pivot Table caches tend to stay put. You might be better off deleting the Pivot Table and starting over, so that all "old" data setups can bee deleted and you only have the latest data. That is why in VBA, you clear the cache and setup a new Pivot on the fly, to avoid the congestion.

When you write "moved to a new location" do you mean on the same worksheet or workbook? That can change things dramatically.

Conditional Formatting of Pivots can add more than just normal conditional formatting...

just some things to consider.... from an old codger. :)
________
PENNY STOCK PICK
 
Last edited:
C

Call_Me_Sam

Chief Imperial Navigator
Local time
Today, 02:06
Joined
Feb 26, 2008
Messages
244
shades said:
Howdy. :pivot Table caches tend to stay put. You might be better off deleting the Pivot Table and starting over, so that all "old" data setups can bee deleted and you only have the latest data. That is why in VBA, you clear the cache and setup a new Pivot on the fly, to avoid the congestion.
Click to expand...

shades, would you recommend rebuilding pivot tables if they have been updated/refreshed almost daily? The ones seem to remember data that isn't there anymore due to the window of reporting moving on? just something else for lemo to consider..
 
L

lemo

Registered User.
Local time
Yesterday, 21:06
Joined
Apr 30, 2008
Messages
187
"moved to a new location" - dragging. just selected a block and dragged it on the same worksheet.

thanks, for the cache tip (no pun), will try to kill that pivot table and redo it, see where it takes me.

now, the data behind the pivot table (on the server) will be updated daily, so whoever opens the file (from our intranet) will always have the most current info. i guess i should include vb code that clears cache on opening?..

thanks,
l
p.s. turns out the reason it took forever to save the file was due to our server, just happened at the same time as the pivot table thing.
 
C

Call_Me_Sam

Chief Imperial Navigator
Local time
Today, 02:06
Joined
Feb 26, 2008
Messages
244
lemo said:
now, the data behind the pivot table (on the server) will be updated daily, so whoever opens the file (from our intranet) will always have the most current info. i guess i should include vb code that clears cache on opening?..
Click to expand...

how would you go about clearing the cache upon opening file?
 
L

lemo

Registered User.
Local time
Yesterday, 21:06
Joined
Apr 30, 2008
Messages
187
no idea!
but i looked online, looks like there are a few tips here and there on how to 'en-lighten' the pivot tables.
of course if you have suggestions i'd be more than happy to hear..
l
 
S

shades

Registered User.
Local time
Yesterday, 20:06
Joined
Mar 25, 2002
Messages
516
If you created the Pivot Table with VBA right after opening, then you could have it built in.

I don't have time to look for the code, but again, check chapter 12 of VBA and Macros for MS Excel for some code.
________
Mercury sable specifications
 
Last edited:
L

lemo

Registered User.
Local time
Yesterday, 21:06
Joined
Apr 30, 2008
Messages
187
thanks shades, will check it out..

right now i am re-creating the file, step by step, and changing pivot filter at every step to see when the time jump occurs. so far it looks like it's NOT the conditional formatting (although it did add a few seconds)..

another peculiar annoyance in this file - takes forever to cut-paste or move even one blank cell, and also to insert columns. don't know if it's related to the other problem..

l
 
L

lemo

Registered User.
Local time
Yesterday, 21:06
Joined
Apr 30, 2008
Messages
187
it's WORD WRAP !

darn word wrap slows everything to a halt! what an utter nuisance..

i would have never guessed...

but - it's not that simple - looks like it does it only in conjunction with automatic row height. still under investigation..
in the meantime -

BEWARE of WORD WRAP !

l
 
You must log in or register to reply here.

Similar threads

H
New to Microsoft Access world, should I invest the time learning it from scratch or go for the C#, SQL Server, and ASP path? Please advice
Replies
17
Views
1,338
gemma-the-husky
gemma-the-husky
P
Approaches to using saved queries for doing calculations on forms
2
Replies
32
Views
1,751
Pat Hartman
P
L
A Question Of Design
Replies
7
Views
938
Pat Hartman
P
B
Solved Entering Length Of Time as hh:mm:ss and saving as seconds
Replies
14
Views
874
Bloodlvst
B
I
Adding working days to a date, not calculating correctly every time
Replies
6
Views
1,144
Integrate
I

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom