Finding matching fields accross multiple worksheets
 

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

Finding matching fields accross multiple worksheets
 

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" a écrit
dans le message de news:
...

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

Finding matching fields accross multiple worksheets
 

Thank you! Worked a treat.


--
alpha417
------------------------------------------------------------------------
alpha417's Profile: http://www.excelforum.com/member.php...o&userid=34969
View this thread: http://www.excelforum.com/showthread...hreadid=547052