Combining elements from several sheets into a new sheet

[Michel Bintener]

Hi there.
While I'm fairly confident with Word and Entourage, I don't know much about
Excel, or at least not enough to solve the problem I've just been confronted
with. Here's the situation: I have an Excel file with a series of sheets,
each sheet containing my students' grades for one particular subject. What
I'd now like to know is whether it is possible to have Excel create a new
sheet, or to have a new view, which lists all the grades of a particular
student. Say for instance that student XY has taken French and English, so
he will figure on both sheets. Is it possible to create some kind of list
which automatically gets student XY's grades from all the different sheets
there are? And if yes, how can it be done? I'm using Excel 2004, all updates
applied, and Excel's Help function is not really helpful since I don't know
how to call the function I'm looking for.
Thank you in advance for your help!

Michel

 

[JE McGimpsey]

What I'd now like to know is whether it is possible to have Excel
create a new sheet, or to have a new view, which lists all the grades
of a particular student. Say for instance that student XY has taken
French and English, so he will figure on both sheets. Is it possible
to create some kind of list which automatically gets student XY's
grades from all the different sheets there are? And if yes, how can
it be done? I'm using Excel 2004, all updates applied, and Excel's
Help function is not really helpful since I don't know how to call
the function I'm looking for.

I'm imagining the following:

Sheet "French":
A B C ... M
1 Name Grade1 Grade2 Final Grade
2 Jane Doe 80 93 92
3 Carl Roe 85 88 84
4 Bill Coe 45 54 62
....

Sheet "English":
A B C ... P
1 Name Grade1 Grade2 Final Grade
2 Jane Doe 98 99 97
3 Bill Coe 76 84 79
4 Joe Moe 87 86 88
....

Then in the summary sheet:

A B C
1 Name English French
2 Bill Coe 62 79
3 Jane Doe 97 92
4 Joe Moe 88
5 Carl Roe 84
....

If that's what you're looking for, there are many, many ways of doing
it. One way:

Summary sheet:

B2: =IF(COUNTIF(English!A:A,A2),VLOOKUP(English!A,A2,16,FALSE),"")
C2: =IF(COUNTIF(French!A:A,A2),VLOOKUP(French!A:M,A2,13,FALSE),"")

Copy down as far as necessary.

There are more sophisticated methods, but without knowing the way your
data is set up, it's hard to guess what would work better.

 

[Michel Bintener]

Summary sheet:

B2: =IF(COUNTIF(English!A:A,A2),VLOOKUP(English!A,A2,16,FALSE),"")
C2: =IF(COUNTIF(French!A:A,A2),VLOOKUP(French!A:M,A2,13,FALSE),"")

Copy down as far as necessary.

There are more sophisticated methods, but without knowing the way your
data is set up, it's hard to guess what would work better.

Thanks very much, JE! Turns out my needs were not as complicated, but I
didn't make it clear in my original post that student XY figures on every
single sheet, so there's no need for a condition. Neverheless, your formula
contained what I was looking for, the expression "<name of the sheet>!" at
the beginning of a cell to refer to elements from another sheet, so I simply
created a summary sheet where the cells refer to the final grade fields on
the different sheets.
But your time has not been wasted, since I will soon be confronted with a
situation where I will need such a condition (i.e. where student XY does not
take every single class), so that's when I'll be able to use all of your
answer. Again, thanks very much!

Michel