If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
dynamic charts - problem with copy
I have a small survey. Before knowing the results, I created for the first
question, on the first worksheet, sample responses and a dynamic chart - a single-series bar chart. For the other questions, I copied that worksheet. The chart on the first sheet works perfectly. The charts on the other sheets do not work properly. The series function for these charts reflects the sheet that the chart is on, but the source data for these charts points back to the original chart in the first worksheet. So, any changes that I make to the first sheet are reflected in all the charts. Any changes that I make to the subsequent sheets do not affect their corresponding charts. Can anyone please tell me what is the problem and its solution? Thanks |
#2
|
|||
|
|||
dynamic charts - problem with copy
The series on the first sheet has a formula that refers to dynamic
names, something like Book1.xls!YRange Note this name has a workbook scope. The chart you copied normally would stay linked to the sheet it is embedded in, because its formulas link to cell addresses, like Sheet1!$A$1:$A$10 Note this address has a worksheet scope. The problem here is a bit complicated. You need to set up a workbook that contains only a master sheet with data and chart. Define the names as worksheet-scope. In Excel =2003 you do this by prefixing the name of the name by the sheet name in the first field of the Define Names dialog: Sheet1!YRange or if the name includes spaces or other bad characters: 'Sheet 1'!YRange In 2007 you can select the scope of the name in the corresponding dialog. In all versions, there's a much better (and free) Name Manager available, at http://jkp-ads.com, which makes it simple to define and redefine names easily. Now define the chart data in terms of this new name. Save the workbook with this master sheet. Move the master sheet into your workbook, and the links remain. Reopen the master workbook, and move the master sheet again into your workbook, and repeat as many times as necessary. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ maggym wrote: I have a small survey. Before knowing the results, I created for the first question, on the first worksheet, sample responses and a dynamic chart - a single-series bar chart. For the other questions, I copied that worksheet. The chart on the first sheet works perfectly. The charts on the other sheets do not work properly. The series function for these charts reflects the sheet that the chart is on, but the source data for these charts points back to the original chart in the first worksheet. So, any changes that I make to the first sheet are reflected in all the charts. Any changes that I make to the subsequent sheets do not affect their corresponding charts. Can anyone please tell me what is the problem and its solution? Thanks |
#3
|
|||
|
|||
dynamic charts - problem with copy
Thanks Jon,
Your solution worked, but it has now created a new problems. In my original workbook, I had included a command button that would generate a copy the first worksheet for the next question. I want that button in the master so that it will be included in each copy of that sheet. I don’t know how to write that code. It should take into account that the master and working workbook are both open at the same time, or that the working workbook is open and the button will open and close the master after making a copy. Doing this in the master file doesn’t seem possible. I am a beginner with VBA, but can manage. The second problem is that I had modified the colors for the master, but your method uses a new workbook with the default palette. The formatting is thus not what I wanted. How do I get the custom color palette transferred to the new workbook along with the copied sheet? While your method worked, I still don’t understand why mine didn’t. As you said, the defined names that I created had a workbook scope. The Series function for each chart in the workbook included the defined names, not absolute references ie Sheet2! Yrange, not Sheet2! $A$1:$A$10. Wouldn’t that just do the trick and link the copied charts to the sheets that they are in? Thanks in advance, Maggy -- mm "Jon Peltier" wrote: The series on the first sheet has a formula that refers to dynamic names, something like Book1.xls!YRange Note this name has a workbook scope. The chart you copied normally would stay linked to the sheet it is embedded in, because its formulas link to cell addresses, like Sheet1!$A$1:$A$10 Note this address has a worksheet scope. The problem here is a bit complicated. You need to set up a workbook that contains only a master sheet with data and chart. Define the names as worksheet-scope. In Excel =2003 you do this by prefixing the name of the name by the sheet name in the first field of the Define Names dialog: Sheet1!YRange or if the name includes spaces or other bad characters: 'Sheet 1'!YRange In 2007 you can select the scope of the name in the corresponding dialog. In all versions, there's a much better (and free) Name Manager available, at http://jkp-ads.com, which makes it simple to define and redefine names easily. Now define the chart data in terms of this new name. Save the workbook with this master sheet. Move the master sheet into your workbook, and the links remain. Reopen the master workbook, and move the master sheet again into your workbook, and repeat as many times as necessary. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ maggym wrote: I have a small survey. Before knowing the results, I created for the first question, on the first worksheet, sample responses and a dynamic chart - a single-series bar chart. For the other questions, I copied that worksheet. The chart on the first sheet works perfectly. The charts on the other sheets do not work properly. The series function for these charts reflects the sheet that the chart is on, but the source data for these charts points back to the original chart in the first worksheet. So, any changes that I make to the first sheet are reflected in all the charts. Any changes that I make to the subsequent sheets do not affect their corresponding charts. Can anyone please tell me what is the problem and its solution? Thanks |
#4
|
|||
|
|||
dynamic charts - problem with copy
You can copy the palette from one workbook to another in Excel 2003:
Tools menu Options Color tab, Copy colors from, and select the workbook with the color palette you want to use. In 2007, you can select the colors from the Page Layout tab, Themes group, Colors dropdown, and choose the theme you want. Any custom themes will appear in the list. Your approach doesn't work in Excel 2003 or 2007. In Excel 2003, when you copy a sheet with a chart that uses names (not addresses) to reference chart data on the worksheet, if the names are workbook-level, the names are retained. If the names are worksheet-level, the names are replaced by arrays of values. In Excel 2007, either type of name is converted to the corresponding address. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ maggym wrote: Thanks Jon, Your solution worked, but it has now created a new problems. In my original workbook, I had included a command button that would generate a copy the first worksheet for the next question. I want that button in the master so that it will be included in each copy of that sheet. I don’t know how to write that code. It should take into account that the master and working workbook are both open at the same time, or that the working workbook is open and the button will open and close the master after making a copy. Doing this in the master file doesn’t seem possible. I am a beginner with VBA, but can manage. The second problem is that I had modified the colors for the master, but your method uses a new workbook with the default palette. The formatting is thus not what I wanted. How do I get the custom color palette transferred to the new workbook along with the copied sheet? While your method worked, I still don’t understand why mine didn’t. As you said, the defined names that I created had a workbook scope. The Series function for each chart in the workbook included the defined names, not absolute references ie Sheet2! Yrange, not Sheet2! $A$1:$A$10. Wouldn’t that just do the trick and link the copied charts to the sheets that they are in? Thanks in advance, Maggy |
Thread Tools | |
Display Modes | |
|
|