Update and sort chart from drop down change

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LDP Analyst - 23 Jan 2008 01:54 GMT

Hello Excel gods, I come to your mountain seeking wisdom.

I have a bar chart with 12 categories that are referenced from a data table,
and that data is filterd from 3 drop downs. I have the chart set up now that
the chart data and graphics update as the drop downs are changed, but my boss
(aka. the man) wants the chart to display largest to smallest absolute
values. I can do all of that but what I don't know how to achieve is to make
it autosort descending just by me changing the drop down menu. Is there a way
to default the data table to automatically sort each time the drop down is
changed?

Reply to this Message

Jon Peltier - 23 Jan 2008 13:17 GMT

Could you set up an autofilter to handle the table of data? Then you could
filter out some records and sort the rest from the dropdown arrows on the
column headers.

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

> Hello Excel gods, I come to your mountain seeking wisdom.
>
[quoted text clipped - 11 lines]
> to default the data table to automatically sort each time the drop down is
> changed?

Reply to this Message

LDP Analyst - 23 Jan 2008 14:36 GMT

Thanks, I used an array of formulas from Jon Mansfield that indexed the
largest from the smallest value. The formula is listed below.

=INDEX(EG$78:EG$88,MATCH(LARGE($EG$78:$EG$88-ROW($EG$78:$EG$88)/10^10,ROWS($EF$78:EG78)),$EG$78:$EG$88-ROW($EG$78:$EG$88)/10^10,0))

I had to make it a single cell array by pressing ctrl-shift-enter at the
same time when the formula was entered.

See: "sort bar chart in excel" in excel chart discussions.

> Could you set up an autofilter to handle the table of data? Then you could
> filter out some records and sort the rest from the dropdown arrows on the
[quoted text clipped - 22 lines]
> > to default the data table to automatically sort each time the drop down is
> > changed?

Reply to this Message