Dynamic/Linked Chart

[Rammy]

Hi All,

I want to create a single chart that changes depending on a word that is selected. For instance, if I had:

John
Bob
George

I would like to have a drop down menu with John, Bob, and George's name.
If I were to select on John, I would have a particular graph associated with John's information to appear. Similarly, if Bob were clicked his graph would appear.

Additionally, the number of names could change could change (i.e. 4, 5,... or more names).

Is this feasible? If it is, could someone help me with a template?

Thanks!

 

[gardnertoo]

There are two ways to do this that come immediately to mind, and probably lots of other ways as well.

Method A: The chart looks at a static range of cells, whose values are dynamic. Create a range of cells somewhere separate from the source data. This area will hold LOOKUP or INDEX/MATCH formulas to go into the source table and pull out the data for the employee you've selected in the dropdown, and your chart will point at this fixed range of cells. Make your formulas look at the current and future range of cells to accommodate changes. See the yellow cells in the example below, where cell I13 is a Data Validation dropdown.

Method B: The chart looks at a dynamic range of cells. Create named ranges for the X and Y values. Define the named ranges using OFFSET formulas which look through the source table for the data associated with the employee you've selected. Make the OFFSET formulas looks beyond the current data set to accommodate changes. Here's the definition for the range I called "chartY" for my test, where cell I3 is a Data Validation dropdown: =OFFSET(Sheet1!$H$5:$H$10,0,MATCH(Sheet1!$I$3,Sheet1!$I$4:$Z$4,0))

Book1
	H	I	J	K
3	Chart who:	John
4	Date	John	Bob	George
5	1/1/2010	960	7	210
6	2/1/2010	355	883	836
7	3/1/2010	892	323	210
8	4/1/2010	485	756	172
9	5/1/2010	319	39	220
10	6/1/2010	35	665	637
11
12
13	Date	Bob
14	1/1/2010	7
15	2/1/2010	883
16	3/1/2010	323
17	4/1/2010	756
18	5/1/2010	39
19	6/1/2010	665
Sheet1