Excel charts not updating automatically

[guibert]

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.

 

[rog1111]

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

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.

 

[cr07]

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.

 

[rog1111]

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

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.

 

[A Dashing Blade]

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.

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

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

 

[rog1111]

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

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

 

[Glen2002]

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

 

[JamesAndExcel]

Excel graphs not updating

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

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!

 

[darthvaler]

i have got the same problem with an Excel file. is it possible that there's no solution to that problem?

 

[AnEdge]

You can fix that by using VBA to have the charts updae...to learn about VBA check out:

Pearson Software Consulting

One of the best resources on Excel/VBA.