Changing plot order in series formulas

[Leslie]

In the charting newsgroup, I have recently sought and obtained much help under the general heading of automating changes in the order of columns and/or stacks. Increased understanding on my part of what is involved in the matter ultimately led me to raise a question similar to that raised in the material which follows, but that question hasn't been answered. I suspect that that's because the question's buried under a blizzard of earlier questions and answers

I have therefore taken the liberty of rephrasing my question, including the necessary background information, and posting it here as a stand-alone query

I have a worksheet created using Excel 2002. It has values in the following 15 cells: B2; C3; D4; E5; F6; G7; H7; I8; J8; K8; L8; M9; N10; O10; AND P10

Each such value constitutes a separate series on an accompanying column chart. Those 15 series are plotted on the chart from 1 to 15 in the order given above

Three of the columns on the chart are stacked ones. One such column consists of the G7 and H7 series; another consists of the I8, J8, K8 and L8 series; and the last consists of the N10, O10 and P10 series

As created, the series in the 3 stacked columns have been plotted in descending order of value, with the plot order of the various series in the 3 stacked columns being as follows

G7 is 6 and H7 is 7

I8 is 8, J8 is 9, K8 is 10 and L8 is 11; an

N10 is 13; O10 is 14 and P10 is 15

The values of the cells in the worksheet change regularly. If the values of the cells which produce any of the 3 stacked columns cease to be plotted from largest to smallest, I would like the plot order of the series concerned to be changed, so that the chart will continue to show the stacks in the relevant column with the largest stack on the x axis and the other(s) being stacked on top of it in decreasing order of value

What I envision is a sub-procedure which will check the values of, for example, the cells in row 7. If that check shows that the value of H7 is now greater than that of G7, the sub-procedure will change the series formulas of the two series concerned so that the plot order of G7 will now be 7 and of H7 will now be 6

I'd very much appreciate being told whether such a sub-procedure can be created and, if so, how

Also, if it can be created, I assume that it will involve the use of the PlotOrder Property. The help screen in Excel for that property says that plot order can only be set within a chart group.

My chart has two chart groups, with the series N10, O10 and P10 constituting a separate chart group from the rest. (I assume that's because only N10, O10 and P10 are plotted against a secondary y axix.) I don't know enough to know whether that means that there would have to be a separate sub-procedure for the cells in row 10

 

[Tom Ogilvy]

Seems easier and more dynamic to create a second set of cells that use
formulas to produce the values in the order you wish. Have your chart refer
to those cells and plot them. You can use the Large or small function
against your sets of cells.

--
Regards,
Tom Ogilvy

In the charting newsgroup, I have recently sought and obtained much help

under the general heading of automating changes in the order of columns
and/or stacks. Increased understanding on my part of what is involved in the
matter ultimately led me to raise a question similar to that raised in the
material which follows, but that question hasn't been answered. I suspect
that that's because the question's buried under a blizzard of earlier
questions and answers.

I have therefore taken the liberty of rephrasing my question, including

the necessary background information, and posting it here as a stand-alone
query.

I have a worksheet created using Excel 2002. It has values in the

following 15 cells: B2; C3; D4; E5; F6; G7; H7; I8; J8; K8; L8; M9; N10;
O10; AND P10.

Each such value constitutes a separate series on an accompanying column

chart. Those 15 series are plotted on the chart from 1 to 15 in the order
given above.

Three of the columns on the chart are stacked ones. One such column

consists of the G7 and H7 series; another consists of the I8, J8, K8 and L8
series; and the last consists of the N10, O10 and P10 series.

As created, the series in the 3 stacked columns have been plotted in

descending order of value, with the plot order of the various series in the
3 stacked columns being as follows:

G7 is 6 and H7 is 7;

I8 is 8, J8 is 9, K8 is 10 and L8 is 11; and

N10 is 13; O10 is 14 and P10 is 15.

The values of the cells in the worksheet change regularly. If the values

of the cells which produce any of the 3 stacked columns cease to be plotted
from largest to smallest, I would like the plot order of the series
concerned to be changed, so that the chart will continue to show the stacks
in the relevant column with the largest stack on the x axis and the other(s)
being stacked on top of it in decreasing order of value.

What I envision is a sub-procedure which will check the values of, for

example, the cells in row 7. If that check shows that the value of H7 is now
greater than that of G7, the sub-procedure will change the series formulas
of the two series concerned so that the plot order of G7 will now be 7 and
of H7 will now be 6.

I'd very much appreciate being told whether such a sub-procedure can be created and, if so, how.

Also, if it can be created, I assume that it will involve the use of the

PlotOrder Property. The help screen in Excel for that property says that
plot order can only be set within a chart group.

My chart has two chart groups, with the series N10, O10 and P10

constituting a separate chart group from the rest. (I assume that's because
only N10, O10 and P10 are plotted against a secondary y axix.) I don't know
enough to know whether that means that there would have to be a separate
sub-procedure for the cells in row 10.

 

[Leslie]

As I had understood the matter (though dimly, I admit), the only way to
change the order in which a number of series appear on a chart is to change
the plot orders in their respective series formulas. Unless those plot
orders are to be changed manually, my further understanding is that their
changing must be done in VBA, rather than through the use of worksheet
functions.

If I'm wrong about that, I'd be happy to use worksheet functions, but I'd
need to be spoonfed about that just as much as I'd need to be spoonfed if
using VBA.

Any contributions of either sort gratefully received!

 

[Tom Ogilvy]

If each individual cell is a separate series, which is what I understood
your situation to be, using worksheet functions, you are not changing the
order of the series. You are changing which value is placed in each
series - the objective being to have the number in the series appear in the
order you want them plotted. So the graph doesn't change, the series don't
change, the values in the series change. The lower value is placed in the
lower plotted series and so forth.