Hi,
I have a major problem with uploading charts on my excel-file and I truly appreciate all help on this issue.
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.
I have built the charts as follows:
=SERIES("variable1_name";'filename.xls'!xaxisrangename;'filename.xls'! yaxisrangename;1)
and
=SERIES("variable2_name";'filename.xls'!xaxisrangename;'filename.xls'! yaxisrangename;3)
etc.
The following observations hold true:
- When I filter data and excel calculates the reference cells everything is correct in terms of the output numbers in named ranges
- There should be no circular references in the calculations, mistakes in formulas, or mistakes in links to other worksheets
- When I make changes to the worksheets a "calculating"-message appears to the lower left corner of excel --> there is a substantial amount of calculations
- The charts update when I save, close and reopen the file
- 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)
- The model including the charts work in Excel 2007 and later versions. Unfortunately using them is not an option here.
I have tried several tricks recommended in forum threads with similar problems:
- Changed calculations as both automatic and manual (F9) in excel options
- Unhidden all rows and columns ( --> charts worked for one time on each worksheet and then froze again)
- Redone the charts using named references
- Checked that all charts are connected to a data range (when chart is pushed data area is highlighted)
Thank you very much for your help and advice on this issue!
Br,
Teemu Kärnä
Bookmarks