Excel 2007 - Line chart where secondary Y axis uses Primary X Axis

[luckyajr]

Hi All,

Thank you in advance for viewing this thread!

I have created a macro that will plot a line graph based upon 5 series. This works well, however, I would also like to add a vertical line that is based upon a value in the primary X-axis.
For example, the X values may range from 50 to 100 and I want to place a vertical line on "65". (The ranges will change each time the spreadsheet is used and will be extremely volatile and thus could be well beyond this example range).
I am able to create one manually with an XY scatterplot using a secondary axis, but then the secondary X-axis has a different min and max than the primary X-axis, and I don't want to have to manually change the min and max ranges each time I run the spreadsheet. And if I set the min and max to "auto" then the secondary is still different than the primary.
I'm also familiar with the option of changing the primary chart type to an XY scatter and then adding the secondary XY to it and thus the x-axes are the same. However, with this option my primary X-axis range doesn't format correctly. I can't have it "fixed" because the range will change with each spreadsheet use (and I don't want the user to have to manually change it) and if it is set to "auto" it usually starts at "1" and then goes beyond the range data. And I am wanting to chart only the given range and nothing more.
Your thoughts are sincerely appreciated and welcome. Thank you!

 

[Steve=True]

add another 2 series to the 2nd axis 1 with a line at your minimum of 1st axis and one with a line at the maximum of the 1st axis and set the line color to None.

This will set your region to be the same as the first axis and not show the line.

Kinda like a dummy series.

 

[luckyajr]

Thank you for the reply. I trialed this, and it still sets the secondary X-axis at zero, which is not the primary minimum. Any thoughts? Thanks again.

 

[Steve=True]

Make sure your new series = the minumum of your data points from the series on the 1st axis.

Also make sure your new series is really on the 2nd axis.

Alternately, you can make these dummy series larger or smaller than you think the data will ever reach and add these to the 2nd axis and the 1st axis to normalize them both.

Alternately, post a file and we will try and help. www.4shared.com

Steve=True