Finding matching fields accross multiple worksheets

[alpha417]

Hi,

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 average.

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!

[Ardus Petus]

With colum headers in row 1 holding each exam's sheet name:
=VLOOKUP($A2,INDIRECT(B$1&"!A:B"),2,0)

See example: http://cjoint.com/?fFnItF3OqH

HTH
--
AP


"alpha417" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Hi,
>
> 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
> average.
>
> 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
> ------------------------------------------------------------------------
> alpha417's Profile:
> http://www.excelforum.com/member.php...o&userid=34969
> View this thread: http://www.excelforum.com/showthread...hreadid=547052
>

[alpha417]

Thank you! Worked a treat.