MrExcel.com - Your One Stop for Excel Tips & Solutions

Chart Range

Posted by steve on February 11, 2002 8:54 AM


Hello, *trying* to creat a Chart on a varying size
range. For example I imported these 3 columns of numbers
starting at A1

1/21/2002 2.414214 4.383049
1/22/2002 3.732051 4.383049
1/23/2002 3.732051 4.09845

I selected "CurrentRegion" from the Edit menu. I inserted
a Chart and customized chart type and all the labels, etc.
I saved this as a template, Chart.xlt

Now when I open a New document, I can create a New spreadsheet
with my customized chart format, (Chart.xl) but it will only graph the 3
data series above, i.e, only range A1.C3

If I imported (or cut-and-pasted) data like this

1/21/2002 2.414214 4.383049
1/22/2002 3.732051 4.383049
1/23/2002 3.732051 4.09845
1/24/2002 3.146264 4.157029
1/25/2002 4.236068 4.230234
1/26/2002 4.236068 4.230234

I would now like my customized Chart to graph A1.F3, but
it still only graphs A1.C3, ignoring any data beyond the
original range when I created the template file.

Is this not possible with a template ?
What do I have to do to get Excel to graph the CurrentRegion ?

thanks

- steve

Posted by Derek on February 11, 2002 9:56 PM

Steve
I had a similar problem.
Say my source data was in A1:B6.
In C1 I put a formula like this "A1:"&"B"&Counta(B:B) to give me the expanding range address of my source data.
I then tried to get the chart to accept this formula as the source area =Indirect(C1).
It didn't seem to like this so I wrote a macro telling it to do virtually the same thing i.e.

ActiveChart.SetSourceData Source:=Sheets("sheetname").Range(Range("sheetname!C1").Value), PlotBy:= etc

It seems perfectly happy with this so I now have a macro button labelled "Update Chart" which works brilliantly.
I obtained the code by using the recorder.
Hope this helps you
Derek

Hello, *trying* to creat a Chart on a varying size range. For example I imported these 3 columns of numbers starting at A1 1/22/2002 3.732051 4.383049 1/23/2002 3.732051 4.09845 a Chart and customized chart type and all the labels, etc. I saved this as a template, Chart.xlt with my customized chart format, (Chart.xl) but it will only graph the 3 data series above, i.e, only range A1.C3 1/22/2002 3.732051 4.383049 1/23/2002 3.732051 4.09845 1/24/2002 3.146264 4.157029 1/25/2002 4.236068 4.230234 1/26/2002 4.236068 4.230234 it still only graphs A1.C3, ignoring any data beyond the original range when I created the template file. What do I have to do to get Excel to graph the CurrentRegion ? - steve

Posted by steve on February 12, 2002 9:44 AM


Thanks Derek - the thing about that solution -
and it DOES work - is that along with a new
source of data, any Series configurations I had in the
template become reset to defaults (i.e., I lose them)

So the solution for me was to create the template with
source data having the largest number of rows I could
think of (for ex: 500) and customize the chart based upon
some 500 rows of dummy data. Then I delete the range and
set current cell to A1 and save the XLT template.

As long as any new WKS I open within this template has LESS
info (i.e., fewer than 500 rows) then the chart seems to like
it and scales the x and y axis according to the data. And I
get to keep my Series and legend customizations.

- steve

Steve I had a similar problem. Say my source data was in A1:B6. In C1 I put a formula like this "A1:"&"B"&Counta(B:B) to give me the expanding range address of my source data. I then tried to get the chart to accept this formula as the source area =Indirect(C1). It didn't seem to like this so I wrote a macro telling it to do virtually the same thing i.e. I obtained the code by using the recorder. Hope this helps you Derek