How to add a Vertical Line to a Column or Line Chart with two axes already in use?

[Alan]

Hi All,

I need to add a vertical line to a column / line chart combo where the
secondary axis is already in use.

I have ready through John Peltier's site
(http://peltiertech.com/Excel/Charts/ComboCharts.html) and
specifically the two options he has under the 'Special Effects and
Features' section for adding vertical lines to a chart, but both
require the use of a secondary axis which is then hidden. That is not
an option for me as I am already using that secondary axis.

I am currently using a line object drawn over the top of the chart
object and manually positioned on the chart as per this image:

http://img143.imageshack.us/img143/5541/verticallineonexcelcombfm9.png

Is there any way to programmatically determine the location that I
would need to put the line object to have it divide between two points
on the x-axis (say, between Nov 2006 and Dec 2006), or to add it to
the chart object itself without losing what is already there?

Thanks,

Alan.
--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

 

[Jon Peltier]

Describe your chart. The extra axes are not strictly necessary, as an XY
series can share axes with many other kinds of chart. It's just easier for
those inexperienced in charting to use the secondary axis.

- Jon

 

[Alan]

Describe your chart. The extra axes are not strictly necessary, as
an XY series can share axes with many other kinds of chart. It's
just easier for those inexperienced in charting to use the
secondary axis.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Hi Jon,

Thank you for your reply.

I am not sure what you mean by, "Describe your chart".

Did you look at the graphic I posted the link to which is a PNG of the
chart as it currently works (with the line object manually positioned
over the chart object)?

http://img143.imageshack.us/img143/5541/verticallineonexcelcombfm9.png

Obviously I *could* combine both the monthly and annualised totals
onto one axis, but the differences in scale (around a factor of 10)
would make that less than desireable as the monthly (line chart)
figures would be 'scrunched' into a small section at the bottom of the
chart and would be hard to read.

Thanks,

Alan.
--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

 

[Jon Peltier]

Well, duh, I never clicked on the link.

Double click on the X axis. What is the Base Unit? If it is Day(s), you can
simply plot your XY series using the desired date. If it is Month(s), you
need to convert the date to elapsed months. October 2006 comes out to the
1281st month (2006-1900)*12+(10-1). To put a vertical line between October
and November 2006, use X=1281.5 for your XY series.

- Jon

 

[Alan]

Well, duh, I never clicked on the link.

Double click on the X axis. What is the Base Unit? If it is Day(s),
you can simply plot your XY series using the desired date. If it is
Month(s), you need to convert the date to elapsed months. October
2006 comes out to the 1281st month (2006-1900)*12+(10-1). To put a
vertical line between October and November 2006, use X=1281.5 for
your XY series.

Jon,

I know you are probably immune to the thanks and praise, but I am
seriously impressed!

Thank you, thank you, thank you!!

Alan.

--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

 

[Jon Peltier]

You're welcome. I meant to add, because of the ease of use of dates rather
than computing months, I almost always use Day(s) as my Base Unit.

- Jon

 

[Alan]

You're welcome. I meant to add, because of the ease of use of dates
rather than computing months, I almost always use Day(s) as my Base
Unit.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

No problem - months works fine in this case, but I will bear it in
mind for future reference.

Thanks again,

Alan.

--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb