After serious thinking wrote :
Hi

When I say class I mean a different sheet, each class teacher e.g the teacher
of grade 8a or grade 8b gets a copy of this sheet into which they type their
marks.So in order to eventually get the stats I was asking about , would it
not b easier to paste or import all the other sheets into one sheet and then
try to write some macro to add up all the different codes per subject

After additional review of your project I may have an idea for a simple
solution:

You can add subject columns to the 'Names' sheet (or use a new
'SummaryScores' sheet) and use formulas to collect the scores for each
term. This would require adding local defined names to each subject
sheet for the learners list and score columns, as well as the names
list on the summary sheet.

Example:
On Sheets("SummaryScores"):
Make sure the names list is configured EXACTLY the same as on the
subject sheets.
Select the entire list of learner names;
In the NameBox (left side of FormulaBar) type...
'SummaryScores'!Learners
..and press the 'Enter' key

Repeat this for each subject sheet, substituting the sheet tab name
respectively!


Repeat for each term column of each subject sheet:

Example for Sheets("Maths"):
Select the entire results column for Term1;
In the NameBox type 'Maths'!Term1

Repeat for each remaining term.


On Sheets("SummaryScores"):
Select the entire Maths:Term1 column and enter the following formula...
=SUMIF(Maths!Learners,Learners,Maths!Term1)
...hold down the 'Ctrl' key and press the 'Enter' key.

Select the entire Maths:Term2 column and enter the following formula...
=SUMIF(Maths!Learners,Learners,Maths!Term2)
...hold down the 'Ctrl' key and press the 'Enter' key.

...and so on.

Optionally, you could just collect the values in the Final Mark column
if you don't want/need the term marks on this sheet.

Note that using SUMIF is more concise than using a lookup function and
will return zero instead of an error. If you don't want to see zeros
you can use ConditionalFormatting to change the font color to match the
cell shade.

Now all you need is an overall final mark column to average the subject
scores.

--
Garry

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