Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
I have created a workbook to use as a weekly reporting tool for different
sites across the country. It contains a "Report" sheet and a "Data" sheet. There are 4 charts embedded into the Report sheet. I have set-up the Data sheet to have the data titles in Column A, a 12-week summary in Column B, and the weekly data begins at Column C. For the 12-week, I used the formulas =sum($C$1:$P$1) and on the Chart series, I used =Data!$C$1:$P$1. I designed the sheet so a center would insert a column at Column C each week for the most recent data. Unfortunately, everytime they insert the column, the formulas do stay static to Column C, instead they change to $D$1:$P$1. Question 1: Is there a way to make the formulas in both the 12-week summary and the Charts stay at columns C:P and not change everytime a column gets inserted? Since I couldn't get that to work yet on my own, I tried to create a macro that would change all of the formulas back and then recreate the chart and place it in the correct location on the "Report" sheet. I get an error everytime it runs. It will change the formauls, but it won't format the chart properly and instead stops the macro with a "Run-time error 1004: Unable to get the ChartObjects property of the worksheet class." Question2: If thre isn't a way to make the Cell ranges static, how can I replace existing charts with new ones using a macro so every center can simply run the same macro without needing to make any manual changes to the charts? Thanks for any guidance you can provide! -- -Mark |
#2
![]() |
|||
|
|||
![]()
Mark -
You don't need a macro. Try making a dynamic chart, using defined range names that don't keep moving. For example, to define $C$1:$P$1 when someone might insert a new column before the existing column C, try a range name like this. On the Insert menu, select Name Define. Enter a name like rngC1P1 in the name box, and in refers to, enter this formula: =OFFSET($B$1,0,1,1,14) which means (using the arguments left to right) define the range which relative to $B$1, starts zero rows down and one column right, is one row high and 14 columns wide. As long as $B$1 isn't changed by row or column insertions, you're cool. when making the chart, you have to define the ranges for each series separately. In step 2 of the chart wizard, or on the Source Data dialog, go to the Series tab. In place of =Sheet1!$C$1:$P$1 in the range selection boxes, enter =Sheet1!rngC1P1. More examples and links: http://peltiertech.com/Excel/Charts/Dynamics.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Mark wrote: I have created a workbook to use as a weekly reporting tool for different sites across the country. It contains a "Report" sheet and a "Data" sheet. There are 4 charts embedded into the Report sheet. I have set-up the Data sheet to have the data titles in Column A, a 12-week summary in Column B, and the weekly data begins at Column C. For the 12-week, I used the formulas =sum($C$1:$P$1) and on the Chart series, I used =Data!$C$1:$P$1. I designed the sheet so a center would insert a column at Column C each week for the most recent data. Unfortunately, everytime they insert the column, the formulas do stay static to Column C, instead they change to $D$1:$P$1. Question 1: Is there a way to make the formulas in both the 12-week summary and the Charts stay at columns C:P and not change everytime a column gets inserted? Since I couldn't get that to work yet on my own, I tried to create a macro that would change all of the formulas back and then recreate the chart and place it in the correct location on the "Report" sheet. I get an error everytime it runs. It will change the formauls, but it won't format the chart properly and instead stops the macro with a "Run-time error 1004: Unable to get the ChartObjects property of the worksheet class." Question2: If thre isn't a way to make the Cell ranges static, how can I replace existing charts with new ones using a macro so every center can simply run the same macro without needing to make any manual changes to the charts? Thanks for any guidance you can provide! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Data Range Mess | Charts and Charting in Excel | |||
Chart attached to dynamic range | Charts and Charting in Excel | |||
Activating a Chart object | Charts and Charting in Excel | |||
how to change range for dynamic chart in excel 2000 with button? | Charts and Charting in Excel | |||
Chart DataSource Change | Charts and Charting in Excel |