using excel as a markbook

[rattlebee88]

I am trying to create a 'markbook' template for my school (math
faculty) in order to input all our exam marks and take the work out o
manually calculating adjusted marks etc., and making something quit
'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 summar
sheet. The summary sheet takes marks from each student's information i
the 8 sheets and combines them into a long list of all the students (w
often do common testing and need to see all students together). Thi
long list also adjusts all the marks to the required percentage t
calculate the overall mark for the semester
What my big issue with all of this is, is that we often have student
change classes based on an improvement or decline in ability. I'
wondering how (or if) I can keep formulas in my summary sheet workin
when I move a student between sheets

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

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

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

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

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

Thank

 

[GS]

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/t...uments-calendars-more-FX101741961.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