Dynamic chart and shrinking text boxes

  • Thread starter Thread starter beeawwb
  • Start date Start date
B

beeawwb

Good afternoon all,

I have a dynamic chart, that records the amount of forms processed eac
week. Every Friday, I coallate the statistics for the past week, an
input them in, and the chart then grows by one week. Perfectly norma
stuff, documented regularly.

What I need to know, is, if it is possible to link outside objects t
parts of the chart.

Example. During 02/01/04 and 23/01/04 we were down 1 FTE (full tim
equivalent), and I have a text box between those dates indicating this
The problem is, when I add on a new week (for example, Week Endin
06/02/04) all the dates shift backwards, and I have to resize the tex
box.

So, is it possible to link the starting end of a box to 1 "gridline
and the ending point of a box to another, so that it would always b
between 02/01/04 and 23/01/04, no matter how big or small the ga
between the two is.

Thanks for any help you can give,

-Bo
 
Bob -

It's hard to keep a textbox where you want it, because it likes to
wander. Use a dummy series instead, with X and Y data configured to put
a label where you want it, and use datalabels on that series. You can
link a datalabel to the text in a cell, by adding datalabels to the
series, selecting a label (one click for the labels for the entire
series, another click for the specific label), pressing = and then
clicking on the cell with the mouse. If you have a bunch to do, download
Rob Bovey's Chart labeler (http://appspro or http://bmsltd.co.uk in the
MVP section) or John Walkenbach's Chart Tools, both free Excel addins
that apply an entire range of labels to a series.

- Jon
 
Yes, I have chart labeller, used it for some ABS charting a while ago
which made a huge difference to me. Unfortunately, I can't (read: don'
particularly want to) use a dummy chart for labels, as the labels hav
to be actually off of the line graph. These charts get printed weekl
as transparencies, and they're already cluttered enough, so the label
are underneath the X-Axis. I honestly didn't think there was any way t
do what I want, but thought I'd ask anyway. I've learned so muc
already using this NG / Forums, it's great opportunities to learn wha
Excel can and can't do.

Thanks anyway. :)

-Bo
 
Bob -

Another way to indicate the time period is to use a column chart which
shows up on those dates but is zero outside of them. Make the columns as
wide as the space between them (gap width on the options tab = 0), and
use a transparent rectangle as the custom pattern for the series (see
http://www.j-walk.com/ss/excel/usertips/tip071.htm).

But you don't want another series for labels, so you probably don't want
one for this either. Maybe then a macro to relocate the text boxes.

- Jon
 
Back
Top