Dynamic selection - a step too far?

[anonymoose]

I've been driving myself insane this morning trying as many different ways of creating a dynamic graph based on a variable dataset.

What I'm looking to do is allow users to select a school (defined by SCH_ID), probably via a combobox and have excel find and bring back the approriate school data and chart D through to I (in this case 100% stacked).

However, my problem is that the NCY varies between school, so one school could be -1 to 6, another 0 to 4, another 5 - 7 etc.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11.5pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">SCH_ID</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">SCH_NAME</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">NCY</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">NCY_Desc</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Cat1</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Cat3</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Cat4</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Cat5</TD><TD style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Cat6</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2002</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">-1</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">Nursery</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2002</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">Reception</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">21</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">28</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2002</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">Year 1</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2002</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">Year 2</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">22</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2002</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">Year 3</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">25</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">21</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2002</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">Year 4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">28</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2006</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">Reception</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2006</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">Year 1</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2006</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">Year 2</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2006</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">Year 3</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2006</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">Year 4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2006</TD><TD style="FONT-SIZE: 10pt"> </TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">Year 5</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: center">0</TD></TR></TBODY></TABLE>

Does this even make sense!?! It did at 10am this morning

Any thoughts are welcome!

 

[Von Pookie]

Maybe I'm missing something--but would the information in column C really matter? I would think that as long as the Description in column D was set correctly that's all you'd really need. You would grab the school ID, and use the info in col D as the labels. The actual *data* is E:I.

Edit: Have you thought of trying a pivot table/chart?

 

[anonymoose]

Hi

Thanks for the feedback

You are correct, column C is not required in the excel sheet, it was a copy and paste into excel from the access database where all the matching takes place - in our tables NCY is the main column and is matched to NCY_Desc for reporting in Access.

My description wasn't the best, you are correct that D is the labels; however, the number of labels will be different for different schools, so I can't just have a preset list of labels as I don't want the blank lines on the graph.

Hmmmmm, pivot charts; not ever used them before but happy to give it a go - thanks!