Excel 2003 does not refresh charts - how to resolve?

[teekar]

Hi,

I have faced a major problem with refreshing charts on my excel file:

I have been building an Excel 2003 based model, which has several worksheets in it with hundreds of columns and rows of interconnected data, formulas and 2-4 charts on each worksheet. Until recently the charts refreshed and updated according to the changes in the named ranges when I filtered the underlying data. However, now the charts do not update anymore. </SPAN>

I have built the charts as follows:</SPAN>

=SERIES("variable1_name";'filename.xls'!xaxisrangename;'filename.xls'! yaxisrangename;1)</SPAN>
and </SPAN>
=SERIES("variable2_name";'filename.xls'!xaxisrangename;'filename.xls'! yaxisrangename;2)</SPAN>
etc.</SPAN>

The following observations hold true:</SPAN>

• When I filter data and excel calculates the reference cells everything is correct in terms of the output numbers in named ranges</SPAN>
• There should be no circular references in the calculations, mistakes in formulas, or mistakes in links to other worksheets</SPAN>
• The charts update when I save, close and reopen the file</SPAN>
• Charts update when I click on a data point in the chart, activate the formula-builder and push Enter (no changes made to the SERIES formula)</SPAN>
• Furthermore, the model including the charts work in Excel 2007 and later versions. Unfortunately using them is not an option here.</SPAN>

I have tried several tricks recommended in forum threads with similar problems:</SPAN>

- Changed calculations as both automatic and manual (F9) in excel options</SPAN>
- Unhidden all rows and columns (--></SPAN></SPAN> charts worked for one time on each worksheet and then froze again)</SPAN>
- Redone the charts using named references</SPAN>
- Checked that all charts are connected to a data range (when chart is pushed data area is highlighted)


Thank you very much for your ideas on how to resolve this issue!

Br,

Teemu Kärnä</SPAN>