I'm writing this in response both to Tushar's and Jon's latest posts.

By way of preface, I mention I'm always very anxious to find as soon as
possible a reply to any question I post in these newsgroups, although I'm
aware that any answerers are unlikely to feel the same way about further
posts of mine! However, I do feel guilty if I don't reply immediately, if
only to express thanks for the assistance I'm always getting. This time I
plead guilty with an explanation (two, actually): computer problems and a
(human) virus.

I know that I'm a fool who rushed in where angels feared to tread, but once
I discovered that there was in VBA something called the PlotOrder property,
which seemed tailor-made to produce the automatic switching of series I was
interested in, I thought that using it (if only I could figure out how) must
be better than using such complex combinations of worksheet functions as
were obviously required in an attempt to achieve the same result. Whether
directly or indirectly, I've been told I'm wrong by everyone who's answered
any of my questions on the topic and I think the time must now have come to
give up on the idea.

When my head is clear enough and assuming my computer is truly working
properly again, I'll knuckle down to trying to adapt Tushar's latest
formulas to my precise situation.

With thanks,

Leslie

"Jon Peltier" wrote in message
...
Leslie -

The columns will not reposition themselves over a different category
label just by changing the plot order. You would have to somehow
rearrange or sort the data so the data moved within its worksheet range.

I didn't read Tushar's explanation in detail, but his formulas probably
do the rearranging for you.

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

Leslie: wrote:

Jon,

The chart I'm concerned with has columns only. Six of them are based
on one series only, so I've described those as "simple" columns. The
remaining three are based on either two, three or four series, so
I've described those as "stacked" columns. The chart type I selected
in the chart wizard was the "stacked columns" subtype. However, I
arranged the data in the source worksheet in an unusual staggered
fashion which permitted the mixing of simple and stacked columns in
the one chart. I discovered that method from others. You may have
been one of those others or, if not, you at least have in the past
drawn people's attention to the method and pointed them to other
sources for it.

Two more things about the chart are that: first, the extreme
righthand column, which is one of my three "stacked" ones, is plotted
against a secondary y axis; and, secondly, to be able to show data
labels at the top of all of my columns, including the "stacked" ones,
I created invisible columns above the visible ones and of the same
value as the visible ones and put data labels inside the bases of
those invisible columns.

When I try to manipulate one of my "stacked" columns on the primary y
axis by changing the plot orders of the series formulas of the series
involved, the stacks jump around within the column just as desired.

However, when I try to manipulate my leftmost "simple" column on the
primary y axis by changing the plot order of its series formula from
1 to 2, the change in number shows up (and the next column's plot
order, that column also being a "simple" column, changes to 1), but
the columns stand still.

Perhaps the chart has some peculiarity of construction which prevents
me from switching simple columns by changing plot orders of their
series formulas. If so, I'll give up on the idea of automating
movement of columns or parts of columns in the chart.

Leslie