PLeeeeease help. How to do combo line-column chart

[rdionne]

I need to display vertical lines in my column chart to mark various limits. I know how to do the column chart programmatically, but how do I add the line chart and make vertical lines with it? Is it possible?

 

[rdionne]

Good point. I tried to do just that and it became very complicated quickly.Can I do a bar chart and then add extra data series and change their chart type to XY? Can this be done programmatically? I'm creating about 40 charts at a time.

Thanks for the help. You da man!

 

[Mark W.]

I'd just create an XY Chart from the beginning. Here are the commands captured by the Macro Recorder...

ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B6"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ChartType = xlColumnClustered

 

[rdionne]

I tried your method. I created an XY type chart and added my vertical lines, and then added my histogram data, and selected and changed it to a column chart. That seems to work fine. The problem is that I want the X values from the histogram data series to be the values on the X axis. I can't seem to be able to do that. I keep getting data from the XY series on the X axis.

 

[Mark W.]

Does your SERIES function look like...

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$6,Sheet1!$B$2:$B$6,1)

...where your X-axis values are in column A and your y-axis values are in column B?
This message was edited by Mark W. on 2002-02-25 16:05

 

[rdionne]

The column data looks like this:

=SERIES(,HistChartData!$A$1:$A$31,HistChartData!$B$1:$B$31,3)

The vertical line XY data looks like this:

=SERIES(,HistChartData!$C$1:$C$2,HistChartData!$D$1:$D$2,1)

 

[Mark W.]

On 2002-02-25 16:18, rdionne wrote:
The column data looks like this:

=SERIES(,HistChartData!$A$1:$A$31,HistChartData!$B$1:$B$31,3)

The vertical line XY data looks like this:

=SERIES(,HistChartData!$C$1:$C$2,HistChartData!$D$1:$D$2,1)

Somewhere you got an extra SERIES (that's may not be visible) on your chart!! I suspect this because the last arguments in both SERIES functions should be consecutive (i.e., 1, 2). Try again. Create an XY Chart using your histogram values. Next, add-in your vertical lines. Finally, selecting 1st the histogram values assign a Column chart type. Lastly, format the your vertical lines to your liking.

 

[rdionne]

I did as you said. The problem is that the vertical line series' don't appear to be on the same X axis values as the histogram data. My histogram data goes from say -.2 to +2.3 If I plot a vertical line at (1.0,0) to (1.0,180) the line appears at the extreme left side of the chart. It should appear somewhere near the middle of the chart range.

 

[Mark W.]

Reset the chart type of the entire chart area (Format Chart Area) to XY Chart. Select any 1 of the histogram values and set the chart type to Column. Select a single value of your vertical line and re-Format Data Series with the addition of a line pattern.
This message was edited by Mark W. on 2002-02-26 07:52

 

[Mark W.]

Once you get this chart finalized I'd recommend that you save its format as a custom, user defined chart type. This will ease the creation of subsequent charts.

 

[Guest]

I still don't get it. As soon as I change the histogram data to a column type, the vertical lines go to the extreme left of the plot area. I tried to reformat the line data series but I can't seem to be able to get the lines to use the same category axis. Thanks for all your help. Can you please be more specific about reformating the line data series as you described?