Source Range Data changing based on drop-down menu

Hello,

Is it possible to have the range for the source data for a chart
change when a value is changed in a drop-down menu. Will be easier if
I explain. I have a table on sheet 1 laid out as below

A B C D E
F G H etc
1 Jan Feb Mar Apr May
Jun Jul
2 Jim 34 44 36 45
55 34 36
3 Bob 54 46 54 34
54 65 45
4 Ann 34 23 52 23
65 45 76
etc

On sheet 2 I have a line chart, with a series using B2:H2 as the
source data (showing Jim's numbers Jan through July). Also on sheet 2
I have a drop-down list of all of the names in column A of sheet 1. I
would like it so when I choose a different name from the drop-down
list, the source data of the chart change to reflect the range of data
of the person whose name I chose (ie If I choose Ann's name from the
drop-down list the source data of the chart will change from B2:H2 to
B4:H4. I have a formula bringing over just one piece of data (not in
the chart) which works when a different name is chosen from the drop-
down =SUM(IF(MTD!$A$2:$A$85=G3,MTD!$S$2:$S$85,0),0), but that formula
wont allow a multi-column range of data for the chart. Really
struggling with this one. Any help would be GREATLY appreciated.

On Sat, 8 Mar 2008, in microsoft.public.excel.charting,
agrandstaff@hotmail.com said:
>Is it possible to have the range for the source data for a chart
>change when a value is changed in a drop-down menu.

Have a look at this article:

http://www.juiceanalytics.com/
writing/2007/05/why-make-100-charts-when-one-will-do/

--
Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
which goes to a spam folder-- please send your email to del3 instead.

More examples, though Juice's is pretty nice:

http://peltiertech.com/Excel/Charts/ChartByControl.html
http://pubs.logicalexpressions.com/...icle.asp?ID=246
http://peltiertech.com/Excel/Charts/ParallelCoord.html

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


"Del Cotter" <del@branta.demon.co.uk> wrote in message
news:JseP+cCRh60HFwF9@branta.demon.co.uk...
> On Sat, 8 Mar 2008, in microsoft.public.excel.charting,
> agrandstaff@hotmail.com said:
>>Is it possible to have the range for the source data for a chart
>>change when a value is changed in a drop-down menu.
>
> Have a look at this article:
>
> http://www.juiceanalytics.com/
> writing/2007/05/why-make-100-charts-when-one-will-do/
>
> --
> Del Cotter
> NB Personal replies to this post will send email to
> del@branta.demon.co.uk,
> which goes to a spam folder-- please send your email to del3 instead.

Thanks for responding guys. Worked very well Jon. Can't thank you enough.

"Jon Peltier" wrote:

> More examples, though Juice's is pretty nice:
>
> http://peltiertech.com/Excel/Charts/ChartByControl.html
> http://pubs.logicalexpressions.com/...icle.asp?ID=246
> http://peltiertech.com/Excel/Charts/ParallelCoord.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Del Cotter" <del@branta.demon.co.uk> wrote in message
> news:JseP+cCRh60HFwF9@branta.demon.co.uk...
> > On Sat, 8 Mar 2008, in microsoft.public.excel.charting,
> > agrandstaff@hotmail.com said:
> >>Is it possible to have the range for the source data for a chart
> >>change when a value is changed in a drop-down menu.
> >
> > Have a look at this article:
> >
> > http://www.juiceanalytics.com/
> > writing/2007/05/why-make-100-charts-when-one-will-do/
> >
> > --
> > Del Cotter
> > NB Personal replies to this post will send email to
> > del@branta.demon.co.uk,
> > which goes to a spam folder-- please send your email to del3 instead.
>
>
>