Change the chart range with a Macro /2007

[marish86]

Hi, I need to create a macro that changes the range of a chart one row down. I basically have data for the whole year but only want the chart to show YTD so I can´t do that with a Dynamic Chart, besides I think a macro will be more useful in this case.. Can anyone please help????

Thanks in advanced

 

[tusharm]

No need for a macro. See
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/

 

[marish86]

Thanks tusharm. How can I create a dynamic chart if I have two categories? For example, the first category is year and the second is month, then I have 3 different series (these I do know how to add). Thanks!

 

[tusharm]

How are your data laid out?

Thanks tusharm. How can I create a dynamic chart if I have two categories? For example, the first category is year and the second is month, then I have 3 different series (these I do know how to add). Thanks!

 

[marish86]

The data is laid out like this, year and month are the categories and "A" and "B" are the series. I know how to create the dynamic chart with Month and Series "A" and "B" but can´t figure out how to add the Year as well (in the multi-level format)...

<table width="240" border="0" cellpadding="0" cellspacing="0"><col style="width: 41pt;" width="55"> <col style="width: 32pt;" width="43"> <col style="width: 52pt;" width="69"> <col style="width: 55pt;" width="73"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; width: 41pt;" width="55" height="17">Year</td> <td style="width: 32pt;" width="43">Month</td> <td class="xl68" style="width: 52pt;" width="69"> A</td> <td class="xl68" style="width: 55pt;" width="73"> B</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">2010</td> <td class="xl67">Jan</td> <td class="xl66" align="right">100</td> <td align="right">56</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Feb</td> <td class="xl66" align="right">102</td> <td align="right">90</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Mar</td> <td class="xl66" align="right">94</td> <td align="right">120</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Apr</td> <td class="xl66" align="right">100</td> <td align="right">175</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">May</td> <td class="xl66" align="right">112</td> <td align="right">124</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Jun</td> <td class="xl66" align="right">117</td> <td align="right">135</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Jul</td> <td class="xl66" align="right">109</td> <td align="right">125</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Aug</td> <td class="xl66" align="right">129</td> <td align="right">151</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Sep</td> <td class="xl66" align="right">145</td> <td align="right">71</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Oct</td> <td class="xl66" align="right">166</td> <td align="right">76</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Nov</td> <td class="xl66" align="right">154</td> <td align="right">88</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Dec</td> <td class="xl66" align="right">143</td> <td align="right">94</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">2011</td> <td class="xl65">Jan</td> <td class="xl66" align="right">141</td> <td align="right">190</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Feb</td> <td class="xl66" align="right">140</td> <td align="right">150</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Mar</td> <td class="xl66" align="right">166</td> <td align="right">182</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Apr</td> <td class="xl66" align="right">157</td> <td align="right">183</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">May</td> <td class="xl66" align="right">46</td> <td align="right">71</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Jun</td> <td class="xl66" align="right">14</td> <td align="right">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Jul</td> <td class="xl66" align="right">22</td> <td align="right">11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Aug</td> <td class="xl66" align="right">9</td> <td align="right">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Sep</td> <td class="xl66" align="right">4</td> <td align="right">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Oct</td> <td class="xl66" align="right">2</td> <td align="right">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Nov</td> <td class="xl66" align="right">1</td> <td align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt;" height="17">
</td> <td class="xl65">Dec</td> <td class="xl66" align="right">1</td> <td align="right">0</td> </tr> </tbody></table>