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.

Source Range Data changing based on drop-down menu
 

On Sat, 8 Mar 2008, in microsoft.public.excel.charting,
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 ,
which goes to a spam folder-- please send your email to del3 instead.

Source Range Data changing based on drop-down menu
 

More examples, though Juice's is pretty nice:

http://peltiertech.com/Excel/Charts/ChartByControl.html
http://pubs.logicalexpressions.com/P...cle.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" wrote in message
...
On Sat, 8 Mar 2008, in microsoft.public.excel.charting,
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
,
which goes to a spam folder-- please send your email to del3 instead.

Source Range Data changing based on drop-down menu
 

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/P...cle.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" wrote in message
...
On Sat, 8 Mar 2008, in microsoft.public.excel.charting,
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
,
which goes to a spam folder-- please send your email to del3 instead.