Thread: Dynamic Chart to record classroom progress

I'm trying to build an excel book that records the progress of up to 20 children in a class and displays the results as a graph for each child. Progress is measured with a single score each half term, and compared with a 'norm', in each of three areas: reading, writing and maths.

The data entry table is straightforward. But I can't get the graphs to update dynamically. I want line graphs that change if the data is changed, and that add a segment as each half-term's data is added. Ive created a work book with a page for each child, and four graphs for each child - combined, reading, writing, maths. Each graph contains the individual child's data together with the graph line for the norm.Child Progress Chart (version 3).xlsx

It's got beyond me, and I've spent ages reading online and got muddled up with VBA, Macros, and dynamic tables until my head hurts. So I'm turning to the forum for help.

I am using Excel 2010 for Macintosh, and I attach a copy of the workbook I've created. It's not good, but then if it was, I wouldn't need this forum!

Please could someone help me sort it out.

Couple questions.
1)Is there a reason you were using all the SUM formulas when you were just referencing one number value from the 'Score sheet'?
2)Why is it that each spread sheet is referencing the same cells from 'Score sheet'? Was it just what you edited before adding it here?

Answer 1. I wanted all the data for all the children on sheet one. Makes collecting it easier. Then I wanted each child's data represented on a graph on their own individual page. At the time of struggling with it, I only knew one way of doing it. But I've continued to read, and have made some progress. See revised chart attached.

Answer 2. I only got as far as child two in that first chart. Again, see revised chart attached.

Child Progress Chart (version 5).xlsx

The current issue is that Excel appears to put the data points in the middle of the term, and not on the line at the end of the term. I'm sure there must be a simple solution to this, but I can't find it.

KB

Ok. One more question, because this affects the formula. You have "Child 1" listed as "Alexander" in the first set of data, but "Anthony" in the other two. Which is it?

It should have been Alexander for all three. But these are only example names and could be anything when the workbook goes live. I will do one for each class. And thank you for your help.

Sorry I haven't posted anything recently. Been crazy swamped at work. I'm playing around with it and I will let you know if I find a solution for it ASAP.