Excel charts not updating automatically

G

guibert

Newbie
Messages
9
Likes
0
I am using large Excel 2002 spreadsheets - circa 50k rows.
After the worksheet recalculates (e.g if I change any values), I have found that the associated charts are not automatically updating. They are still the same as they were before the worksheet was changed. I can only get them to update by making an adjustment to the source data series in the chart itself (any adjustment will do - it seems to be the simple act of changing something that triggers the update). Manual adjustment is a pain in the neck, particularly as I am dealing with a large number of charts.

Automatic calculation is set to 'on' in the spreadsheet, so this can't be the problem. Excel has all relevant service pack updates.

Any ideas on how to crack this would be appreciated.
 
are you using excel vba ? Or just the excel sheet ? If using vba you could use the sheet change event to force a chart refresh

rog1111

guibert said:
I am using large Excel 2002 spreadsheets - circa 50k rows.
After the worksheet recalculates (e.g if I change any values), I have found that the associated charts are not automatically updating. They are still the same as they were before the worksheet was changed. I can only get them to update by making an adjustment to the source data series in the chart itself (any adjustment will do - it seems to be the simple act of changing something that triggers the update). Manual adjustment is a pain in the neck, particularly as I am dealing with a large number of charts.

Automatic calculation is set to 'on' in the spreadsheet, so this can't be the problem. Excel has all relevant service pack updates.

Any ideas on how to crack this would be appreciated.
Click to expand...
 
Excel charts sometimes fail to update

Hi,

I have just experienced exactly the same problem with Excel 2003. Large file, the data series in the charts are described by named ranges. Almost all the time, when I update the data, the charts refresh to reflect this. However sometimes (randomly) the charts do not update.

I can write a macro as a work-around for this (effectively forcing the ranges to double calculate in order to refresh), however, did anyone ever get to the root of this problem?

Thanks.
 
I think that it's probably another of Excel's little quirks. For example, opening a s/sheet from Excel via File/Open is quite not the same as browsing for a file in Windows Explorer and clicking on it.

rog1111

cr07 said:
Hi,

I have just experienced exactly the same problem with Excel 2003. Large file, the data series in the charts are described by named ranges. Almost all the time, when I update the data, the charts refresh to reflect this. However sometimes (randomly) the charts do not update.

I can write a macro as a work-around for this (effectively forcing the ranges to double calculate in order to refresh), however, did anyone ever get to the root of this problem?

Thanks.
Click to expand...
 
cr07 - bear in mind that there's the vba command Application.CalculateFullRebuild which forces the instance of XL to fullty rebuild the calculation dependency tree.

rog1111 said:
I think that it's probably another of Excel's little quirks. For example, opening a s/sheet from Excel via File/Open is quite not the same as browsing for a file in Windows Explorer and clicking on it.
rog1111
Click to expand...

Would disagree Rog, certainly both the Worksheet_Open event and an AutoOpen macro both fire off.



lots of useful tips here . . .
http://www.willowsolutions.com/tips/tips.shtml

partic this one for a simple chart update technique
http://www.willowsolutions.com/tips/tips_2000_03.shtml
 
DB, agreed, but not quite what I meant, the differences are quite subtle eg default template location etc, it's quirky. Not sure about Excel 2007 mind....

Rgds

A Dashing Blade said:
cr07 - bear in mind that there's the vba command Application.CalculateFullRebuild which forces the instance of XL to fullty rebuild the calculation dependency tree.



Would disagree Rog, certainly both the Worksheet_Open event and an AutoOpen macro both fire off.



lots of useful tips here . . .
http://www.willowsolutions.com/tips/tips.shtml

partic this one for a simple chart update technique
http://www.willowsolutions.com/tips/tips_2000_03.shtml
Click to expand...
 
Update Excel

Using the older versions of Excel, this was a problem when the spreadsheet got too large. If I recall a solution was to go to the Tools menu, then Calculations and check the box for Inerations. If that does not work, it may be necessary to bump up the number by 100 at a time.

It worked for me.

Glen2002
 
Excel graphs not updating

rog1111 said:
DB, agreed, but not quite what I meant, the differences are quite subtle eg default template location etc, it's quirky. Not sure about Excel 2007 mind....

Rgds
Click to expand...

Most of the graphs in my Excel file have stopped updating and any newly created wont update unless I click in source data and change from rows to columns and back.
Graphs created that depend on random functions still update as normal.
I tried increasing the number of iterations to 10000 but no difference.
I tried running the macro Application.CalculateFullRebuild but no difference.
This is becomming a major problem and any help would be much appreciated!
 
i have got the same problem with an Excel file. is it possible that there's no solution to that problem?
 
You must log in or register to reply here.

Similar threads

N
How can I automatically import and update data in Excel?
Replies
3
Views
3K
nansen
N
N
IG API – programmer required
Replies
2
Views
3K
Amazon
A
J
FTSE 100 data feed for excel and setup
Replies
2
Views
7K
A Dashing Blade
A Dashing Blade
D
Chart points into Excel
Replies
2
Views
1K
DugDug
D
P
Excel
Replies
2
Views
3K
clin
C
Top