Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
I did some googling this weekend and found lots of helpful advice on how to
make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
I have the exact same problem which I posted here a few days ago, but have
had no responses. There is another post here as well with what seems to be the same problem with no replies. The problem is there but no one seems to know what to do about it. "Ken Snyder" wrote: I did some googling this weekend and found lots of helpful advice on how to make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
Another annoyance with this problem (I'd be interested if you can confirm
that you have the same behaviour), is that if you save to Excel 2003 format and then open in Excel 2003 it pegs the CPU to 100% never to recover. :^( Ken "Larry F" wrote: I have the exact same problem which I posted here a few days ago, but have had no responses. There is another post here as well with what seems to be the same problem with no replies. The problem is there but no one seems to know what to do about it. "Ken Snyder" wrote: I did some googling this weekend and found lots of helpful advice on how to make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
No, I didn't get the same behavior, though to be clear - I don't have 2003 on
the same computer where I have 2007 installed. I was told that having both versions on the same computer can cause problems. So I used v 2007 to save the file on my v 2003 computer and reopened it on the v 2003 box. "Ken Snyder" wrote: Another annoyance with this problem (I'd be interested if you can confirm that you have the same behaviour), is that if you save to Excel 2003 format and then open in Excel 2003 it pegs the CPU to 100% never to recover. :^( Ken "Larry F" wrote: I have the exact same problem which I posted here a few days ago, but have had no responses. There is another post here as well with what seems to be the same problem with no replies. The problem is there but no one seems to know what to do about it. "Ken Snyder" wrote: I did some googling this weekend and found lots of helpful advice on how to make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
That's what i did as well but with the aforementioned results. Bugger. Well
here's hoping some wise excel guru can help out of our common problem and my isolated one. :^) "Larry F" wrote: No, I didn't get the same behavior, though to be clear - I don't have 2003 on the same computer where I have 2007 installed. I was told that having both versions on the same computer can cause problems. So I used v 2007 to save the file on my v 2003 computer and reopened it on the v 2003 box. "Ken Snyder" wrote: Another annoyance with this problem (I'd be interested if you can confirm that you have the same behaviour), is that if you save to Excel 2003 format and then open in Excel 2003 it pegs the CPU to 100% never to recover. :^( Ken "Larry F" wrote: I have the exact same problem which I posted here a few days ago, but have had no responses. There is another post here as well with what seems to be the same problem with no replies. The problem is there but no one seems to know what to do about it. "Ken Snyder" wrote: I did some googling this weekend and found lots of helpful advice on how to make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
I have seen a similar problem, not with the original dynamic chart, but with
a copy made of a dynamic chart. If the dynamic names are scoped to the workbook as yours are, the references to the workbook names are obliterated, converted to [0]. The corrupted references can only be seen through the Edit Source Data dialog, because the series formula is not longer displayed. I discovered two things. 1. If you use worksheet-scoped names, this corruption doesn't occur. 2. If you save, close, and reopen the workbook with corrupted charts, the corruption has healed itself, and the charts are as good as the originals. I've also filed a very detailed bug report on this behavior. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I did some googling this weekend and found lots of helpful advice on how to make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
I may not fully understand your reponse but if I did then I'm not sure this
solves my problem. Basically what I do originally is specify something like: =Data!rChartDates Where "Data" is the tab that has the data in it. If I go back to the chart's properties after making this change it has automatically updated the reference to look like this: ='F&O Analysis v2.1.xlsx'!rChartDates It will stay with this type of reference until i close the spreadsheet and reopen. At which point i get the behavior I explained and the reference now looks like: =[0]!rChartDates If i go in and replace the [0] with "Data" then it relinks and becomes dynamic. THis, however, is not very <idynamic</i if you see what i mean. :^) "Jon Peltier" wrote: I have seen a similar problem, not with the original dynamic chart, but with a copy made of a dynamic chart. If the dynamic names are scoped to the workbook as yours are, the references to the workbook names are obliterated, converted to [0]. The corrupted references can only be seen through the Edit Source Data dialog, because the series formula is not longer displayed. I discovered two things. 1. If you use worksheet-scoped names, this corruption doesn't occur. 2. If you save, close, and reopen the workbook with corrupted charts, the corruption has healed itself, and the charts are as good as the originals. I've also filed a very detailed bug report on this behavior. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I did some googling this weekend and found lots of helpful advice on how to make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
When you define your range names in the first place, the dialog has a Scope
dropdown, which initially says Workbook. Pick the worksheet name (Data) from this dropdown to define a worksheet-scoped name. This one wasn't corrupted in my tests. The reference never changes to the workbook, but stays linked to the worksheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I may not fully understand your reponse but if I did then I'm not sure this solves my problem. Basically what I do originally is specify something like: =Data!rChartDates Where "Data" is the tab that has the data in it. If I go back to the chart's properties after making this change it has automatically updated the reference to look like this: ='F&O Analysis v2.1.xlsx'!rChartDates It will stay with this type of reference until i close the spreadsheet and reopen. At which point i get the behavior I explained and the reference now looks like: =[0]!rChartDates If i go in and replace the [0] with "Data" then it relinks and becomes dynamic. THis, however, is not very <idynamic</i if you see what i mean. :^) "Jon Peltier" wrote: I have seen a similar problem, not with the original dynamic chart, but with a copy made of a dynamic chart. If the dynamic names are scoped to the workbook as yours are, the references to the workbook names are obliterated, converted to [0]. The corrupted references can only be seen through the Edit Source Data dialog, because the series formula is not longer displayed. I discovered two things. 1. If you use worksheet-scoped names, this corruption doesn't occur. 2. If you save, close, and reopen the workbook with corrupted charts, the corruption has healed itself, and the charts are as good as the originals. I've also filed a very detailed bug report on this behavior. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I did some googling this weekend and found lots of helpful advice on how to make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
Jon,
I did as you suggested, but while the reverence doesn't get corrupted, the graph still doesn't update to reflect the recalculated data in the named range??? "Jon Peltier" wrote: When you define your range names in the first place, the dialog has a Scope dropdown, which initially says Workbook. Pick the worksheet name (Data) from this dropdown to define a worksheet-scoped name. This one wasn't corrupted in my tests. The reference never changes to the workbook, but stays linked to the worksheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I may not fully understand your reponse but if I did then I'm not sure this solves my problem. Basically what I do originally is specify something like: =Data!rChartDates Where "Data" is the tab that has the data in it. If I go back to the chart's properties after making this change it has automatically updated the reference to look like this: ='F&O Analysis v2.1.xlsx'!rChartDates It will stay with this type of reference until i close the spreadsheet and reopen. At which point i get the behavior I explained and the reference now looks like: =[0]!rChartDates If i go in and replace the [0] with "Data" then it relinks and becomes dynamic. THis, however, is not very <idynamic</i if you see what i mean. :^) "Jon Peltier" wrote: I have seen a similar problem, not with the original dynamic chart, but with a copy made of a dynamic chart. If the dynamic names are scoped to the workbook as yours are, the references to the workbook names are obliterated, converted to [0]. The corrupted references can only be seen through the Edit Source Data dialog, because the series formula is not longer displayed. I discovered two things. 1. If you use worksheet-scoped names, this corruption doesn't occur. 2. If you save, close, and reopen the workbook with corrupted charts, the corruption has healed itself, and the charts are as good as the originals. I've also filed a very detailed bug report on this behavior. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I did some googling this weekend and found lots of helpful advice on how to make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
Is calculation set to manual? My charts take around 4 seconds to update when
I press F9 (they're based on RAND() to make them dynamic), but then I have overloaded Excel: the file is 34 KB and I have 18 charts on the worksheet, and no other worksheets. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Larry F" wrote in message ... Jon, I did as you suggested, but while the reverence doesn't get corrupted, the graph still doesn't update to reflect the recalculated data in the named range??? "Jon Peltier" wrote: When you define your range names in the first place, the dialog has a Scope dropdown, which initially says Workbook. Pick the worksheet name (Data) from this dropdown to define a worksheet-scoped name. This one wasn't corrupted in my tests. The reference never changes to the workbook, but stays linked to the worksheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I may not fully understand your reponse but if I did then I'm not sure this solves my problem. Basically what I do originally is specify something like: =Data!rChartDates Where "Data" is the tab that has the data in it. If I go back to the chart's properties after making this change it has automatically updated the reference to look like this: ='F&O Analysis v2.1.xlsx'!rChartDates It will stay with this type of reference until i close the spreadsheet and reopen. At which point i get the behavior I explained and the reference now looks like: =[0]!rChartDates If i go in and replace the [0] with "Data" then it relinks and becomes dynamic. THis, however, is not very <idynamic</i if you see what i mean. :^) "Jon Peltier" wrote: I have seen a similar problem, not with the original dynamic chart, but with a copy made of a dynamic chart. If the dynamic names are scoped to the workbook as yours are, the references to the workbook names are obliterated, converted to [0]. The corrupted references can only be seen through the Edit Source Data dialog, because the series formula is not longer displayed. I discovered two things. 1. If you use worksheet-scoped names, this corruption doesn't occur. 2. If you save, close, and reopen the workbook with corrupted charts, the corruption has healed itself, and the charts are as good as the originals. I've also filed a very detailed bug report on this behavior. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I did some googling this weekend and found lots of helpful advice on how to make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
I'll second Larry's findings but at least in my case there are some
interesting complexities in the dynamic behaviour where some named variables refer to other named variables that may be on a different sheet (such as "variables" and "rollups" sheets). So the workbook scope is actually much more ideal (and the only one that really works right now). One side question, why is that once a variable is created you can no longer alter its scope? I have tons of variables and recreating, renaming, and deleting them all to arrive at a more tightly scoped set of variables would be daunting to say the least. "Larry F" wrote: Jon, I did as you suggested, but while the reverence doesn't get corrupted, the graph still doesn't update to reflect the recalculated data in the named range??? "Jon Peltier" wrote: When you define your range names in the first place, the dialog has a Scope dropdown, which initially says Workbook. Pick the worksheet name (Data) from this dropdown to define a worksheet-scoped name. This one wasn't corrupted in my tests. The reference never changes to the workbook, but stays linked to the worksheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I may not fully understand your reponse but if I did then I'm not sure this solves my problem. Basically what I do originally is specify something like: =Data!rChartDates Where "Data" is the tab that has the data in it. If I go back to the chart's properties after making this change it has automatically updated the reference to look like this: ='F&O Analysis v2.1.xlsx'!rChartDates It will stay with this type of reference until i close the spreadsheet and reopen. At which point i get the behavior I explained and the reference now looks like: =[0]!rChartDates If i go in and replace the [0] with "Data" then it relinks and becomes dynamic. THis, however, is not very <idynamic</i if you see what i mean. :^) "Jon Peltier" wrote: I have seen a similar problem, not with the original dynamic chart, but with a copy made of a dynamic chart. If the dynamic names are scoped to the workbook as yours are, the references to the workbook names are obliterated, converted to [0]. The corrupted references can only be seen through the Edit Source Data dialog, because the series formula is not longer displayed. I discovered two things. 1. If you use worksheet-scoped names, this corruption doesn't occur. 2. If you save, close, and reopen the workbook with corrupted charts, the corruption has healed itself, and the charts are as good as the originals. I've also filed a very detailed bug report on this behavior. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I did some googling this weekend and found lots of helpful advice on how to make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
#12
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
Calculation is set to automatic, I based first cell in each column on Rand()*0+
The cells (where the data resides) on the worksheet where the chart is embedded reference other worksheets - on the other worksheets the first cell in each column is also based on Rand()*0+ I have to admit, I'm not sure why the Rand() is now required, in Excel 2003 charting based on other worksheets was straight forward. Frankly, this feels like a lot of nonsense. "Jon Peltier" wrote: Is calculation set to manual? My charts take around 4 seconds to update when I press F9 (they're based on RAND() to make them dynamic), but then I have overloaded Excel: the file is 34 KB and I have 18 charts on the worksheet, and no other worksheets. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Larry F" wrote in message ... Jon, I did as you suggested, but while the reverence doesn't get corrupted, the graph still doesn't update to reflect the recalculated data in the named range??? "Jon Peltier" wrote: When you define your range names in the first place, the dialog has a Scope dropdown, which initially says Workbook. Pick the worksheet name (Data) from this dropdown to define a worksheet-scoped name. This one wasn't corrupted in my tests. The reference never changes to the workbook, but stays linked to the worksheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I may not fully understand your reponse but if I did then I'm not sure this solves my problem. Basically what I do originally is specify something like: =Data!rChartDates Where "Data" is the tab that has the data in it. If I go back to the chart's properties after making this change it has automatically updated the reference to look like this: ='F&O Analysis v2.1.xlsx'!rChartDates It will stay with this type of reference until i close the spreadsheet and reopen. At which point i get the behavior I explained and the reference now looks like: =[0]!rChartDates If i go in and replace the [0] with "Data" then it relinks and becomes dynamic. THis, however, is not very <idynamic</i if you see what i mean. :^) "Jon Peltier" wrote: I have seen a similar problem, not with the original dynamic chart, but with a copy made of a dynamic chart. If the dynamic names are scoped to the workbook as yours are, the references to the workbook names are obliterated, converted to [0]. The corrupted references can only be seen through the Edit Source Data dialog, because the series formula is not longer displayed. I discovered two things. 1. If you use worksheet-scoped names, this corruption doesn't occur. 2. If you save, close, and reopen the workbook with corrupted charts, the corruption has healed itself, and the charts are as good as the originals. I've also filed a very detailed bug report on this behavior. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I did some googling this weekend and found lots of helpful advice on how to make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
#13
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
I only based mine on RAND() so when I pressed F9 I'd get different data, not
to enforce some kind of update. I read about people having problems with charts not updating, and I've never experienced it first hand. So I have no relevant advice. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Larry F" wrote in message ... Calculation is set to automatic, I based first cell in each column on Rand()*0+ The cells (where the data resides) on the worksheet where the chart is embedded reference other worksheets - on the other worksheets the first cell in each column is also based on Rand()*0+ I have to admit, I'm not sure why the Rand() is now required, in Excel 2003 charting based on other worksheets was straight forward. Frankly, this feels like a lot of nonsense. "Jon Peltier" wrote: Is calculation set to manual? My charts take around 4 seconds to update when I press F9 (they're based on RAND() to make them dynamic), but then I have overloaded Excel: the file is 34 KB and I have 18 charts on the worksheet, and no other worksheets. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Larry F" wrote in message ... Jon, I did as you suggested, but while the reverence doesn't get corrupted, the graph still doesn't update to reflect the recalculated data in the named range??? "Jon Peltier" wrote: When you define your range names in the first place, the dialog has a Scope dropdown, which initially says Workbook. Pick the worksheet name (Data) from this dropdown to define a worksheet-scoped name. This one wasn't corrupted in my tests. The reference never changes to the workbook, but stays linked to the worksheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I may not fully understand your reponse but if I did then I'm not sure this solves my problem. Basically what I do originally is specify something like: =Data!rChartDates Where "Data" is the tab that has the data in it. If I go back to the chart's properties after making this change it has automatically updated the reference to look like this: ='F&O Analysis v2.1.xlsx'!rChartDates It will stay with this type of reference until i close the spreadsheet and reopen. At which point i get the behavior I explained and the reference now looks like: =[0]!rChartDates If i go in and replace the [0] with "Data" then it relinks and becomes dynamic. THis, however, is not very <idynamic</i if you see what i mean. :^) "Jon Peltier" wrote: I have seen a similar problem, not with the original dynamic chart, but with a copy made of a dynamic chart. If the dynamic names are scoped to the workbook as yours are, the references to the workbook names are obliterated, converted to [0]. The corrupted references can only be seen through the Edit Source Data dialog, because the series formula is not longer displayed. I discovered two things. 1. If you use worksheet-scoped names, this corruption doesn't occur. 2. If you save, close, and reopen the workbook with corrupted charts, the corruption has healed itself, and the charts are as good as the originals. I've also filed a very detailed bug report on this behavior. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I did some googling this weekend and found lots of helpful advice on how to make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
#14
Posted to microsoft.public.excel.charting
|
|||
|
|||
Dynamic Charts Problem
Download the Name Manager from http://jkp-ads.com. It has functionality far
beyond Excel's native name interface. I believe there's a version for Excel 2007. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I'll second Larry's findings but at least in my case there are some interesting complexities in the dynamic behaviour where some named variables refer to other named variables that may be on a different sheet (such as "variables" and "rollups" sheets). So the workbook scope is actually much more ideal (and the only one that really works right now). One side question, why is that once a variable is created you can no longer alter its scope? I have tons of variables and recreating, renaming, and deleting them all to arrive at a more tightly scoped set of variables would be daunting to say the least. "Larry F" wrote: Jon, I did as you suggested, but while the reverence doesn't get corrupted, the graph still doesn't update to reflect the recalculated data in the named range??? "Jon Peltier" wrote: When you define your range names in the first place, the dialog has a Scope dropdown, which initially says Workbook. Pick the worksheet name (Data) from this dropdown to define a worksheet-scoped name. This one wasn't corrupted in my tests. The reference never changes to the workbook, but stays linked to the worksheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I may not fully understand your reponse but if I did then I'm not sure this solves my problem. Basically what I do originally is specify something like: =Data!rChartDates Where "Data" is the tab that has the data in it. If I go back to the chart's properties after making this change it has automatically updated the reference to look like this: ='F&O Analysis v2.1.xlsx'!rChartDates It will stay with this type of reference until i close the spreadsheet and reopen. At which point i get the behavior I explained and the reference now looks like: =[0]!rChartDates If i go in and replace the [0] with "Data" then it relinks and becomes dynamic. THis, however, is not very <idynamic</i if you see what i mean. :^) "Jon Peltier" wrote: I have seen a similar problem, not with the original dynamic chart, but with a copy made of a dynamic chart. If the dynamic names are scoped to the workbook as yours are, the references to the workbook names are obliterated, converted to [0]. The corrupted references can only be seen through the Edit Source Data dialog, because the series formula is not longer displayed. I discovered two things. 1. If you use worksheet-scoped names, this corruption doesn't occur. 2. If you save, close, and reopen the workbook with corrupted charts, the corruption has healed itself, and the charts are as good as the originals. I've also filed a very detailed bug report on this behavior. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ken Snyder" wrote in message ... I did some googling this weekend and found lots of helpful advice on how to make dynamic charts with named ranges and OFFSET, INDEX, and MATCH functions. Anyway, I was able to successfully create the dynamic charts that I wanted by pointing the series data at a named range. For instance the data range represented on my x-axis is derived from the named variable "rChartDates" which is defined as: =OFFSET(allDates,0,MATCH(StartDate,allDates,1)-1,1,MATCH(EndDate,allDates,1)-MATCH(StartDate,allDates,1)+1) This works fine and when I put a reference into my chart I put the following: ='F&O Analysis v2.1.xlsx'!rChartDates This too works fine but when I close the spreadsheet and reopen it it has lost all dynamic behavior and now the property of the chart looks like this: =[0]!rChartDates Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make dynamic charts more dynamic | Charts and Charting in Excel | |||
dynamic charts | Charts and Charting in Excel | |||
Dynamic Charts | Charts and Charting in Excel | |||
Dynamic Charts | Charts and Charting in Excel | |||
Dynamic Charts | Charts and Charting in Excel |