Automating changes in order of columns and/or stacks

[Edele Soriano]

After much time by me and assistance by people in this
newsgroup, I am
almost finished what I confidently expect to be the only
chart I'll ever
produce in my lifetime. Only one matter remains to be
done, if possible.

The chart displays six simple columns and three stacked
ones. I've arranged
the columns so that the seven columns on the right,
whether simple or
stacked, are shown in ascending order of value from left
to right. I've
arranged the three stacked columns so that the stack with
the biggest value
is at the bottom of each column and the values of the
stacks decrease as one
moves up the column.

Is there any way to have the chart automatically
rearrange itself in the
above fashion if a change in value of either a stack, an
entire column or
both warrants it?

If there is, will it still work if there's displayed
above each column the
value data label of an invisible stack equal in value to
the value of the
entire visible column?

 

[Kar Hwa Hew]

Why make this your last chart? You seem to be getting the
hang of
things...

In any case, the below addresses 9 data points arranged
as 3 stacked
items on the left and 6 clustered columns on the right.

Suppose the three stacked values are in A1:A3. Then, in
B1 enter
=LARGE($A$1:$A$3,ROW()). Copy B1 to B2:B3.

Suppose the six clustered values are in D16. Then, in
E2 -- note,
that is E2 -- enter =LARGE($D$1:$D$6,ROW()-1). Copy E2 to
E3:E7.

Plot B1:B3 in a stacked chart. In step 2 of the wizard,
ensure that
'data in rows' is checked.

Now, select E1:E7 -- note E1 is the starting cell, not
E2. Drag the
selection onto the chart. In the dialog box that pops up,
ensure that
'new series' is selected, 'Series name in first row' is
*not* selected,
and 'data in columns' is selected.

Select the first data point of the 2nd series. To do so,
in the chart
click on the clustered column. Pause. Use the right arrow
key to
select the first point (it will be on top of the stacked
column). Add
a data label from the cell that contains the total of the
stacked
column. For more on how, see the Excel/Tutorials/Data
Labels page of
my web site.