Lock shape location to axis values?

[William DeLeo]

Greetings,

I have a series of line charts (time-x vs value-y) with axis limits an
data ranges updated automatically. I need to come up with a way t
identify certain periods of time with shading. But I don't wish t
simply draw rectangles because I don't want to have to edit th
location of the shapes every time new data is added. Also, I hav
about 30 charts to create and maintain.

So ...

1. Is there a away to associate shape position/size to axis values?

2. If not, are there other charting options that would allow me t
plot time vs value overlayed by bars/columns with widths and location
dictated by the same time-x axis? In other words, I need the locatio
and width of the columns to be determined by x axis data.

Thanks in advance

 

[Greg Wilson]

1. Is there a away to associate shape position/size to axis values?
This can be done but it is rather complex. I don't think you want to go there.

I suggest checking out Jon Peltier's site:
http://peltiertech.com/Excel/Charts/ColoredVerticalBand.html

If you can't find what you need there then perhaps the following:

Although I've never had occasion, I think I would have a 2nd series that has
exactly as many points as the first and make the chart type Column (you can
have more than one chart type plotted). For the area you don't want shaded,
plot zeros, and for the area you want shaded, plot the max chart scaling.
Alternatively, plot a value greater than the max scaling and unselect the
AutoScale option so it doesn't adjust to this value (only shows what you
want). Set the gap width to zero so that it blends into one shaded zone.

You will have to programmatically manipulate the series to stay in sync with
the first series including adjusting for changes in min and max scaling if
they occur. I don't have code that does this but you can use the macro
recorder to get some basic info. To get started:

- Unselect the Y axis Autoscale option and enter an appropriate in place
- Add a series to your chart that has exactly as many points as the first
- Change its chart type to Column
- Right click the series and select Format Data Series
- Select the Patterns tab
- In the Border frame, set the series Border to None
- In the Area frame, select the dersired colour
- Select the Options tab
- Set the gap width to zero

Now play with the values of the new series. Opposite the values in the first
series where you want shading, change the values to >= the max scaling value.
All other values set to zero.

Hope this was of help.

Regards,
Greg

 

[William DeLeo]

Thanks so much! I will check out everything you suggest today. I find
experimenting with this sort of charting frustrating, so your specific
instructions will make a huge difference. Thanks again!!!