Macro to change position of chart labels on line chart

[Shane Henderson]

Hello all,

I have quite a trickly problem to solve and I would appreciate any
suggestions.

I have a dashboard page in a worksheet which shows financial KPIs
(i.e. sales) as forecast this year versus last year. There are a
number of line charts on the dashboard showing the differing
forecasts; the user of the dashboard can choose a business unit to
view.

What I would like to be able to do is to have the chart labels for
these charts automatically change their aligmnent so that they display
properly. I have set the chart labels to display "Above" for the new
forecast and "Below" for the previous forecast, but in cases where the
forecast has been revised downards the labels do not display properly.

So the macro would need to:
- check if the business unit selected has changed
- check each each point on each chart (there are two series) and for
each pair of values for each year, set the alignment of the chart
label to "Above" for the higher value and "Below" for the lower value

Any suggestions about how to write this macro (or whether it's even
possible) or other solutions would be really gratefully appreciated!

Thanks in advance,
Shane

 

[Xt]

Hello all,

I have quite a trickly problem to solve and I would appreciate any
suggestions.

I have a dashboard page in a worksheet which shows financial KPIs
(i.e. sales) as forecast this year versus last year.  There are a
number of line charts on the dashboard showing the differing
forecasts; the user of the dashboard can choose a business unit to
view.

What I would like to be able to do is to have the chart labels for
these charts automatically change their aligmnent so that they display
properly.  I have set the chart labels to display "Above" for the new
forecast and "Below" for the previous forecast, but in cases where the
forecast has been revised downards the labels do not display properly.

So the macro would need to:
- check if the business unit selected has changed
- check each each point on each chart (there are two series) and for
each pair of values for each year, set the alignment of the chart
label to "Above" for the higher value and "Below" for the lower value

Any suggestions about how to write this macro (or whether it's even
possible) or other solutions would be really gratefully appreciated!

Thanks in advance,
Shane

How about a dummy series for each graph series which is a suitable
amount above or below its matching series as calulated from the data.
So if series A is above series B at a particular point, the dummy
series A has say 10 added on to A and dummy series B has 10 taken off
B. And vice versa. Now put the labels onto the dummy points (I am
assuming that you are using ChartLabeller) and shift them using
Labeller so that they are sitting on top of the dummies. The dummies,
of course have no markers.

xt