I am trying to create a 'markbook' template for my school (maths faculty) in order to input all our exam marks and take the work out of manually calculating adjusted marks etc., and making something quite 'foolproof' to stop people messing up other class marks.
What I have so far is 8 sheets for the 8 classes in year 7 and a summary sheet. The summary sheet takes marks from each student's information in the 8 sheets and combines them into a long list of all the students (we often do common testing and need to see all students together). This long list also adjusts all the marks to the required percentage to calculate the overall mark for the semester.
What my big issue with all of this is, is that we often have students change classes based on an improvement or decline in ability. I'm wondering how (or if) I can keep formulas in my summary sheet working when I move a student between sheets.

e.g. Student 1 is in class B and has received 15/30 in the test. He is then moved to to class C. His row will most likely change as each class is listed alphabetically.
This would be in the 'class A' sheet
. 30
Student 1 class B 15

In the summary sheet would be all the students in class A, followed by B, then by C.

Student x class A 10
Student y class A 12
Student 1 class B 10 ---- his mark has been converted ='class B'!D4/'class B'!$D$2*20 by taking his mark and dividing by what the test it out of then changing it to a mark out of 20.
Student 2 class B 8
Student # class C 10.5
Student % class C 5

So if I move student 1 into the class C sheet, is there a way I can get excel to follow this student?
Should my summary sheet be set up somehow just to reflect each sheet somehow? Currently all the name and class inputs have just been copied in.

I hope this makes sense to someone, I can't figure out how else to explain it, and there doesn't seem to be anyone at school who knows enough about Excel to help me...

Thanks

the 30 should be above the 15 to indicate that they are in the same column...

Food for thought...

I've used my own Excel based 'Grades Manager' addin for some years now
and I find when students change classes it's easier to keep their marks
intact for any specific class curriculum, given that different classes
may follow different course outlines. That precludes, then, that a
'summary' sheet for all classes would contain a unique list of student
names/IDs (rows with no duplicate IDs), and a complete (columnar) list
of all items students receive scores for. This serves systems that need
to track one student's progress while that student attends several
classes.

If you're only tracking a single subject (maths) then the task is more
simplified, but nonetheless still complex in that the summary sheet
formulas have to ref many sheets. To make this easier, I suggest
dividing the columnar score items into sections for each sheet so that
each section uses the same ref for the source values. To the right of
the last sect you can collect totals and show final
marks/grades/gpa/percent values. The template you end up with will
probably work better if you implement heavy use of local scope defined
name ranges for use by the formulas on the summary sheet. The formulas
on the summary sheet should take into account the student name & ID in
case, for example, there's 2 students named "John Smith" whether
they're in separate classes or the same class.

Another way to go is to use one sheet per class that tracks scores and
reports final marks/grades/gpa/percent values for the class list, then
just have your summary sheet grab the finals from each class and make
its calcs based on those values. For example, Student1 is in Class1 &
Class2, and so this will have 2 entries on 'Summary'. Students in 1
class only will have 1 entry, students in 3 classes will have 3
entries. The overall 'Final' mark/grade/gpa/percent values will be the
average of total entries per student.

I suggest the template has a minimum number of class sheets that are
designed to allow some flexibility for the number of score
items/sections on each sheet. The summary sheet will be pre-designed to
service the minimum number of class sheets, but have a utility that
allows you to add more class sheets and inserts new summary sections
when more class sheets are added.

Sounds to me like you need to engage the services of a professional
Excel developer if you can't find anything ready-made to run with.
There are lots of student grades templates floating around that you can
download and test drive. Here's some links...

http://www.spreadsheetzone.com/default.aspx

http://office.microsoft.com/en-us/te...61.aspx?CTT=97

This next one (MarksXPress Student Grades Manager for Excel) is an
addin that you can have modified to fit your school's grades
tracking/reporting criteria...
http://www.solutionsxpress.com/products/mxp.htm

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc