Charts won't update

[pwermuth]

Hello,

I was hoping somebody could help me with the following problem:

I have a bunch of pie charts that simply will not update despite the
fact that the source data has changed. For example, the source data
will change from 3, 10, 1 to 10, 4, 0 but the chart itself still shows
a 3, 10, 1 split.

When I close and reopen the file the charts update.

I have no idea whether the following things could be somehow causing
some of the problems, but just in case:
- There is a large number of charts on this sheet - about 150
- The source data is calculated as a countif on a range of data which
in itself is a vlookup on values that are created through a search
macro, i.e. the values change only after the search Macro is run
- I have a Worksheet_Change Macro active which should get activated
many times during the search Macro, since the search Macro deletes and
then places new values
- In the workbook there are a lot of links to external files, so when I
open the workbook I am asked whether I want to update. However, the
charts will be correct after reopening the file even if I choose not to
update any of the data from external files.

I just don't get it. I think it was working fine in the beginning. I
would run the search Macro and the values and charts would update in
front of me. At some point it stopped working and I do not know why.

Thanks,

Peter

 

[bj]

Make sure your <tools><options><calculate> is set for automatic calculations

 

[pwermuth]

I checked that before and that is not the issue.
The charts also won't update if I press F9.

 

[bj]

Unfortunately it sounds as though you have a corrupted file, especially when
you said it worked originally.

It is a risk for any file which is open a lot.

I try to set up my files so that I can regenerate them from scratch every so
often, since almost every file I have used more than 40 or fifty times
started having oddities.

I have never figured out how to uncorrupt files once they have started going
odd.

 

[pwermuth]

Ok, that would be unfortunate obviously.

Can you elaborate a little on regenerating files? How do you do that?
Do you literally rewrite the entire file from scratch? I actually use
Macros to create various (repetious) parts of this file, but still thi
would be a ton of work. Even for future reference: how do you set u
your files for that case

 

[Bernard Liengme]

Click on one of the data series in the chart; now look at Formula Bar. Does
it has entry in form
=SERIES(Sheet1!$C$4,Dynamic.xls!xvalues,Dynamic.xls!zvalues,2)

 

[pwermuth]

Yes,

It looks like this:

=Output!$AW$26:$AX$26


Before I start recreating this file from scratch which would be weeks
worth of work, are you sure this is a corrupted file issue?
Is there really no other way to force the charts to update? They
update if you close and open the workbook, so is there some way to
simulate that?

Peter

 

[pwermuth]

Actually, reading your post more carefully I guess my syntax does look
different.

Why?

 

[pwermuth]

I just wanted to bring this back to the top since I still need help with
this.


If I click on one of the data points, the formula bar will display the
following
=SERIES(,'Part II - Concessions'!$AW$18:$AX$18,'Part II -
Concessions'!$BE$18:$BF$18,1)


I actually set these strings in a Macro using the following code

seriesDataSource = "='" & destinationSheet & "'!R" &
counter & "C" & firstCategoryCountColumn & ":R" & counter & "C" &
lastCategoryCountColumn
labelsDataSource = "='" & destinationSheet & "'!R" &
counter & "C" & firstCategoryNameColumn & ":R" & counter & "C" &
lastCategoryNameColumn

ActiveChart.SeriesCollection(1).XValues = labelsDataSource
ActiveChart.SeriesCollection(1).Values = seriesDataSource

That works fine.
Now when the data WITHIN that range (I am not talking about adding
additional data points) changes the charts do not update.