I have set up a new spreadsheet with pupil's achievement levels in different
subjects. Each subject data is stored on a separate sheet. Each pupil has a
unique ID - which is on each sheet. Is there any way I can set up excel to
combine the results of one pupil acheivements in all subject areas. i.e. will
excel look on each separate sheet and locate the results for a particular
pupil - if so - how? Any help gratefully received.

Here's one set-up to try ..

Link to a sample file at:
http://www.savefile.com/files/2254808
ExtractingFromMultipleSheets_UniqueIDs_JillCurly_n ewusers.xls

Assume the subject sheets' set up (identical in structure) is as follows:

In sheet: Sub1
---------
in cols A to C, data from row2 down

Name UniqID Marks
Pup4 1237 85
Pup1 1234 70
Pup2 1235 69
Pup5 1238 66
Pup3 1236 58

(Let's assume lines are sorted in descending order by marks)

In sheet: Sub2
--------
Name UniqID Marks
Pup1 1234 86
Pup4 1237 78
Pup5 1238 78
Pup3 1236 71
Pup2 1235 57

In sheet: Sub3
-----------
Name UniqID Marks
Pup2 1235 79
Pup5 1238 65
Pup1 1234 61
Pup4 1237 60
Pup3 1236 57

In sheet: Master
-----------
The set up in cols A to E,
data from row2 down is:

Name UniqID Sub1 Sub2 Sub3
Pup1 1234
Pup2 1235
Pup3 1236
Pup4 1237
Pup5 1238
etc

Put in C2:
=INDEX(INDIRECT("'"&C$1&"'!C:C"),MATCH(Master!$B2, INDIRECT("'"&C$1&"'!B:B"),
0))

Copy C2 across to E2, fill down to E6 populate the table

For the sample subject marks in Sub1, Sub2 and Sub3, the formulas will
extract the marks correctly for each pupil's unique id from each subject
sheet to yield:

Name UniqID Sub1 Sub2 Sub3
Pup1 1234 70 86 61
Pup2 1235 69 57 79
Pup3 1236 58 71 57
Pup4 1237 85 78 60
Pup5 1238 66 78 65

Adapt to suit ..

Note that the subject names entered in C1:E1 (i.e.: Sub1, Sub2, Sub3)
must match exactly with the sheetnames
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Jill Curly via OfficeKB.com" wrote in message
...
I have set up a new spreadsheet with pupil's achievement levels in
different
subjects. Each subject data is stored on a separate sheet. Each pupil
has a
unique ID - which is on each sheet. Is there any way I can set up excel
to
combine the results of one pupil acheivements in all subject areas. i.e.
will
excel look on each separate sheet and locate the results for a particular
pupil - if so - how? Any help gratefully received.

Put in C2:

=INDEX(INDIRECT("'"&C$1&"'!C:C"),MATCH(Master!$B2, INDIRECT("'"&C$1&"'!B:B"),
0))

The suggested formula above is okay, but it inadvertently carried
an extraneous sheet reference (Master!) in: Master!$B2
(sorry about that)

Put instead in C2:
=INDEX(INDIRECT("'"&C$1&"'!C:C"),MATCH($B2,INDIREC T("'"&C$1&"'!B:B"),0))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--