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" wrote:

>
> 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
>
>
> --
> pwermuth
> ------------------------------------------------------------------------
> pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997
> View this thread: http://www.excelforum.com/showthread...hreadid=385248
>
>

[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" wrote:

>
> I checked that before and that is not the issue.
> The charts also won't update if I press F9.
>
>
> --
> pwermuth
> ------------------------------------------------------------------------
> pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997
> View this thread: http://www.excelforum.com/showthread...hreadid=385248
>
>

[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 used Macros to create various (repetious) parts of this file, but still this would be a ton of work. Even for future reference: how do you set up 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)

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"bj" <[email protected]> wrote in message
news:[email protected]...
> Make sure your <tools><options><calculate> is set for automatic
> calculations
>
> "pwermuth" wrote:
>
>>
>> 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
>>
>>
>> --
>> pwermuth
>> ------------------------------------------------------------------------
>> pwermuth's Profile:
>> http://www.excelforum.com/member.php...o&userid=24997
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=385248
>>
>>

[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.