Using a chart like a formula...?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey,

I want to track the progress of certain "students". I have their results
from tests that they sit in a register-style worksheet. (Names are in column
A, and scores are under appropriate heading in row 1.)

If I was to create a formula to say add SCORE ONE + SCORE TWO + SCORE
THREE... I would create a formula that looked like;

=SUM(B2:B26). I would then want that to appear in each row, so I would use
the tiny square in the bottom right hand corner and it would copy down the
formula using dynamic cell references that were corresponding.

All fairly simple stuff.

I would like to make ONE GRAPH. This would simply plot their score against
the name of a test in a line graph. This is simple enough to do for ONE
student, but I can't find a way of using the equivilent to the tiny little
square to make ONE for each student. (The numbers of students can become
quite high.)

I have seen something (I'm pretty sure it was in excel) where as the tutor
moved their cursor over the students name a graph would appear as if it was a
comment. (I hope you know whatI mean by this).

The tutor could move their mouse down the list and each time they stopped
excel would create a graph for them using the particular student's data,
based on a "template" (eg. Line graph.)

Does anyone have any idea how this was done.
If you've got any ideas, I would really appreciate some help.

Dan
 
I could imagine doing this with some kind of VBA procedure, but it would
take a couple hours to make it work. Basically, I'd use the
Worksheet_SelectionChange event to trigger the charting procedure. If the
selected cell was in the appropriate range, I'd use the data from that row
to construct a chart, and I'd position it near the selected cell. When a
different cell is selected I'd kill the one chart and draw another. I guess
it's also possible to reuse one chart, and change the data source when the
selection changed; this can even be done with a defined name (dynamic
range). Or you could predefine all the charts, then have each one become
visible when the appropriate cell was selected.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top