Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Charts won't update
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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? -- pwermuth ------------------------------------------------------------------------ pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997 View this thread: http://www.excelforum.com/showthread...hreadid=385248 |
#6
|
|||
|
|||
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.xl s!zvalues,2) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "bj" wrote in message ... 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 |
#7
|
|||
|
|||
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 ------------------------------------------------------------------------ pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997 View this thread: http://www.excelforum.com/showthread...hreadid=385248 |
#8
|
|||
|
|||
Actually, reading your post more carefully I guess my syntax does look different. Why? -- pwermuth ------------------------------------------------------------------------ pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997 View this thread: http://www.excelforum.com/showthread...hreadid=385248 |
#9
|
|||
|
|||
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. -- pwermuth ------------------------------------------------------------------------ pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997 View this thread: http://www.excelforum.com/showthread...hreadid=385248 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Charts Linked to Spreadsheets | Charts and Charting in Excel | |||
Chart/Source Data update problem | Charts and Charting in Excel | |||
Monthly charts do not retail formula row/column | Charts and Charting in Excel | |||
Can't create dynamic charts | Charts and Charting in Excel | |||
Charts don't update when data changes | Charts and Charting in Excel |