Multi variable charting

[wiwchar]

I have a sheet of information that I would like to graph. See the sample below. You can see that there are results for different locations, different dates. There are very few common dates. I need the ability to create either a stacked bar of the 10 variables, a bar chart of the total, a line chart, etc for each location. The date would be along the horizontal axis and does not have to be a date axis at this time. How can I either create separate charts for each location, or create a chart that allows me to select the location in a drop down and will change the chart?

thanks.


Location Date V1 V2 V3 v4 v5 v6 v7 v8 v9 v10 Total
A 06/01/08 1 1 1 0 0 1 0 0 1 1 6
A 12/01/08 1 2 2 0 0 1 1 1 1 1 10
A 02/01/09 2 2 2 1 1 2 1 1 2 1 15
A 07/01/09 2 2 2 1 1 2 1 1 2 2 16
A 01/01/10 2 2 3 2 2 2 2 1 2 1 19
A 11/01/10 2 2 3 2 1 2 2 1 2 1 18
A 12/13/09 2 2 2 2 1 2 2 2 2 2 19
A 06/01/12 2 2 3 2 1 3 2 2 2 2 21
A 12/01/12 3 2 3 2 1 3 2 2 2 2 22
A 06/13/13 3 2 3 3 2 3 2 3 2 2 25
A 04/01/14 3 2 3 3 2 3 2 3 2 2 25
B 06/01/08 1 1 1 1 0 0 1 0 1 1 7
B 11/01/08 2 2 2 1 1 1 1 1 1 1 13
B 07/01/09 2 3 2 1 1 2 1 1 1 1 15
B 12/01/09 2 2 2 2 1 2 1 1 2 1 16
B 06/01/10 3 2 2 2 1 2 1 1 3 2 19
B 09/01/10 3 2 2 2 2 3 1 1 2 2 20
B 03/01/12 3 2 2 2 2 2 2 2 2 2 21
B 10/01/12 3 2 3 2 2 2 2 2 2 3 23
B 04/08/13 3 2 3 3 3 2 2 2 2 3 25
B 11/20/14 3 3 3 3 3 2 2 3 2 3 27
B 05/28/14 3 3 3 3 3 3 2 3 2 3 28
C 11/01/08 0 1 0 0 0 0 0 0 0 0 1
C 05/01/10 1 1 1 1 0 1 1 1 1 0 8
C 11/01/10 1 1 1 1 0 1 1 1 1 1 9
C 04/01/12 1 1 1 1 0 0 1 1 1 1 8
C 12/01/12 1 2 1 1 0 1 1 2 1 1 11
C 06/05/13 1 2 1 1 0 1 1 2 1 2 12
C 12/12/13 1 2 2 1 0 1 1 3 2 2 15
C 06/10/14 1 2 2 1 1 1 2 3 2 2 17
E 04/01/10 1 0 0 0 0 0 0 0 0 1 2
E 09/01/12 1 0 0 1 0 1 0 0 0 0 3
F 04/01/08 1 0 0 0 0 0 0 0 0 1 2
F 07/01/08 1 1 1 0 0 0 1 0 1 1 6
F 11/01/08 1 2 1 0 0 0 1 0 1 2 8
F 03/01/09 1 1 2 0 0 1 2 0 1 2 10
F 05/01/10 2 2 2 0 0 1 2 0 1 2 12
F 02/01/11 2 1 2 0 0 1 2 1 1 2 12
F 05/01/12 2 2 2 1 0 1 2 1 1 2 14
F 11/01/12 2 2 2 1 1 1 2 2 1 2 16
F 04/18/13 2 2 2 1 1 2 2 2 2 2 18
F 10/15/13 2 2 3 2 1 2 2 2 2 2 20
F 04/16/14 2 3 3 2 1 1 1 3 2 3 21

 

[gardnertoo]

I would use named ranges, with formulas to define those ranges based on the dropdown cell. Suppose the dropdown is located in cell P1. Using your sample data I created a named range called chart_dates, defined by this formula:

=OFFSET(Sheet1!$B$2,COUNTIF(Sheet1!$A$2:$A$44,"<"&Sheet1!$P$1),0,COUNTIF(Sheet1!$A$2:$A$44,Sheet1!$P$1),1)

This will be used as the "Series X Values" input for every series. For the first series, V1, I created a named range called chart_V1, defined by this formula:

=OFFSET(Sheet1!$C$2,COUNTIF(Sheet1!$A$2:$A$44,"<"&Sheet1!$P$1),0,COUNTIF(Sheet1!$A$2:$A$44,Sheet1!$P$1),1)