David -
Each series in a line chart uses the same categories. So the first
series uses (e.g.) A2:A101 for X and B2:B101 for Y. If you want to use
C22:C41 for the second series' Y values, and have it plot at the same
categories as B22:B41, you need to offset the first data point with
twenty blank cells. This might mean doing some reconstruction in the
worksheet, if you are trying to avoid plotting any data that might be
present in C2:C21.
The alternative is to make an XY Scatter chart. Now your second series
can use A22:A41 and C22:C41 for its X and Y values. If the categories in
A are actual year values, you can even keep series 1 as a line series,
but add series 2 as an XY Scatter series.
People get tangled up in Excel's use of Line and Scatter terminology.
Many of the problems users have with Line charts and their X axes can be
answered with the suggestion to use XY Scatter charts instead. Either
type of series can be formatted the same, in terms of markers, lines,
etc. The difference is in how the X data is treated: a Scatter chart
treats these as actual numerical values, while a Line chart treats them
as non-numerical categories (or as dates in the mislabeled time-scale
category axis option).
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
David Adamson wrote:
I currently have a chart series that ranges from 0 to 100 (thanks to Jon
Peltier) to reflect years. I can now add another series of data on top of
that. But what I was wondering is how can I change my series so that I can
show only the data for say year 20 to 40.
I have tried changing 'a' to 20 and 'c' to 40 and it retrieves the correct
data but puts the data over year 0 to 19.
Can someone please tell me what I am missing?
Cheers
David
---------------------------
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.Location Whe=xlLocationAsObject, Name:="Spread Data"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 100
.MajorUnit = 10
End With
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
End With
'add new data then delete legend
a = 1
r = 6
With Worksheets("Spread Data")
Set Title_Names1 = .Cells(r, 1)
End With
With ActiveChart
.SeriesCollection.NewSeries
.SeriesCollection(2).Name = Title_Names1
End With
'loop through data and make it update
For c = 1 To 100
With Worksheets("Spread Data")
Set Area_rng1 = Range(.Cells(r, 1 + a), .Cells(r, 2 + c))
End With
With ActiveChart
.SeriesCollection(2).values = Area_rng1
End With
Next c