Chart Not Always Updating

Hi,

I wonder if anyone can help me with the following problem:

I am using Microsoft Excel 2003. There are several charts in the worksheet and the ranges for the series in the charts are defined by named ranges, e.g.:

Values: =FirstNamedRange

where FirstNamedRange is defined as Sheet1!A2:A100.

The values in FirstNamedRange are themselves calculated from other inputs. The problem is that when I recalculate the sheet, changing the values of FirstNamedRange, there are times when the chart does not change.

This happens sporadically. 9 times out of 10 both the values in the range and the chart change. At other times, the values in the range change and the chart doesn't reflect this. If I save the file, close and then reopen, (thus forcing the chart to recalculate?) then the chart looks correct.

I have tried everything I can think of to remedy this, can someone please help!!

Many thanks

Re: Charts Fail To Update

Hi Jim, thanks for the reply (and sorry for taking so long to get back to you, have been away for a few days).

There are no circular references in the file. I've set it to manual calculation simply because it is quite large (circa 7MB) and there are a lot of calculations. Unfortunately I can't post it because there is some sensitive information in it.

So from what I understand now, I need to try to find a way to have the file autocalculating as this might solve the problem? I will try it out and see if that solves things (as the charts were only failing to update sporadically, I may need to use the workbook for a while before I know if the problem has been solved or not...). Alternatively, do you think I could write a property for the worksheet that goes along the lines of "On Worksheet.Calculate redefine the ranges used by the chart", thus forcing the chart to recalc?

Thanks very much for your help. Appreciated.

Re: Charts Fail To Update

Hi Jim,

See sample file posted... With the original file (not the sample), the cells Data!A1:B262 are being pulled in from another application by a macro. Most of the time, when the macro pulls in new data the chart on sheet 'Chart' changes. However, very occasionally, the macro will pull in new data, the calculations in cells Data!A1:D53 will change (and I double check these to make sure), but the chart will not change (i.e. still shows values from the previous run, even though I can see that the range Data!vol has changed).

As I can't run this macro in the sample file, I've inserted an 'alterChart' cell that multiplies the result by a given %, attempting to simulate the data change due to the macro pulling in new values.

I realise the problem is incredibly difficult to pinpoint as I can't dictate when it will occur - it happens sporadically.

Many thanks.

Re: Chart Not Always Updating

Hi Jim,

The calculations are not more complicated than those in the sample, however there are a lot more of them. I tried to post a more comprehensive file, but it goes over the limit of what I can upload.

My reason for setting calculations to manual was because the sheet takes several seconds to calculate (due to its size) and this was becoming increasingly annoying, as typically I would need to change several cells before recalculating (thus the workbook would calculate many times more than was required if I set it to automatic calculation).

I realise there is not much more you can do with only this information, so thank you very much for your help. If I make any progress I will add to this post in the future.

All the best.

Re: Chart Not Always Updating

Stumbled across a couple of posts today where people had similar (but not identical) issues:

http://www.trade2win.com/boards/showthread.php?t=14242 (this was the closest problem but there was no solution)

Cells not updating;
http://www.forums.techguy.org/…pdating-excel-2003-a.html
http://help.lockergnome.com/of…l-2003-ftopict190083.html

The latter two seemed to be calculation tree related.

I have written a macro to act as a work around (basically double calculating the named ranges to make sure they refresh). Will go with this and see if it does the trick...

Thanks.

Re: Chart Not Always Updating

Sub RecalcChartRange(sRange As String)


With ThisWorkbook.Names(sRange)
    .RefersTo = "=" & .RefersToRange.Worksheet.Name & "!" & .RefersToRange.Resize(Data.FindRow - .RefersToRange.Row + 1).Address
End With


End Sub

Data.FindRow just returns the last row number you have in the Data sheet.