Finding matching fields accross multiple worksheets

Giganews Newsgroups
Subject: Finding matching fields accross multiple worksheets
Posted by:  alpha417 (
Date: Wed, 31 May 2006


I am attempting to calculate the average score of a set of students
accross 7 exams, with results stored in different worksheets within one
workbook. I cannot find a function to get excel to a) match which
candidates appear in all 7 sheets and b) for each of those candidates,
grab their results from column B. Once I have done this it's just a
simple case of summing all 7 results in B and dividing by 7 to get the

I have a spreadsheet which contains 7 different worksheets, each of
them containing examination results (column B) for around 400 students
arranged by candidate number (column A). I am trying to use an eighth
sheet to compare the results to obtain an average from the 7 exams. The
complication is that some students have sat exams others have not taken,
so between the 7 sheets there is a core of about 340 students who have
done all 7, and a further 60 who have done between 1-6 exams. However,
where a student has not sat an exam, their candidate number is simply
missing, rather than having a zero entry.

It would therefore appear I need some function to get excel to match
the data in column A of sheet one, with that in sheet 2, 3 etc, then
for each match, to return the result from column B of each sheet.

Is this possible?!

Many thanks!

alpha417's Profile:
View this thread: