Source Range Data changing based on drop-down menu

[agrandstaff]

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.

 

[Del Cotter]

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/

 

[Jon Peltier]

More examples, though Juice's is pretty nice:

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

- Jon

 

[A.C.G.]

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