Here's what I've got:
A 500 Row X 10 Column worksheet
The first column is student names (header "STUDENT NAME")
The second column is grade (header "GRADE")
The third through 10th columns as teacher names ("P1Teach", "P2Teach", etc)

What I want to do is set up an array in a separate sheet that will return
all of the students for which P1Teach is a certain value (i.e. "MOORE").

Any assistance is helpful.

Hi,

Your (shortened) table looks like this so how do we know who is in which
class?

Stu Name Grade P1 Teach P2 Teach etc
mike
joe
wilbur
etc



--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Allen" wrote:

Here's what I've got:
A 500 Row X 10 Column worksheet
The first column is student names (header "STUDENT NAME")
The second column is grade (header "GRADE")
The third through 10th columns as teacher names ("P1Teach", "P2Teach", etc)

What I want to do is set up an array in a separate sheet that will return
all of the students for which P1Teach is a certain value (i.e. "MOORE").

Any assistance is helpful.

a) Can you not just use Autofilter? If you turn autofiltering on, you can
select the specific value ("MOORE") in the "P1Teach" column, and get a list
of students that match.

b) You could create a pivot table of your data on a separate sheet and
manipulate the data in many ways.

HTH,

Eric


"Allen" wrote:

Here's what I've got:
A 500 Row X 10 Column worksheet
The first column is student names (header "STUDENT NAME")
The second column is grade (header "GRADE")
The third through 10th columns as teacher names ("P1Teach", "P2Teach", etc)

What I want to do is set up an array in a separate sheet that will return
all of the students for which P1Teach is a certain value (i.e. "MOORE").

Any assistance is helpful.

StudentName Grade P1Teach P2Teach etc
Mike 6 Moore Taylor
Sam 8 Great Good
Heather 6 Moore Good
Teressa 8 Great Taylor

So I want a formula that I can place in a separate worksheet that will
return these lists:
Mike
Heather (for first period Moore)

Sam
Teressa (for first period Great)

etc. for second period teachers

The number of students in each class varies.

Thanks.

BTW, EricG, I'll look into the Pivot Table idea, I haven't used one before.

Allen.

"Mike H" wrote:

Hi,

Your (shortened) table looks like this so how do we know who is in which
class?

Stu Name Grade P1 Teach P2 Teach etc
mike
joe
wilbur
etc



--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Allen" wrote:

Here's what I've got:
A 500 Row X 10 Column worksheet
The first column is student names (header "STUDENT NAME")
The second column is grade (header "GRADE")
The third through 10th columns as teacher names ("P1Teach", "P2Teach", etc)

What I want to do is set up an array in a separate sheet that will return
all of the students for which P1Teach is a certain value (i.e. "MOORE").

Any assistance is helpful.

I'm not sure but could that be what you are looking for ?
http://img715.imageshack.us/img715/3881/nonamel.png
Micky


"Allen" wrote:

Here's what I've got:
A 500 Row X 10 Column worksheet
The first column is student names (header "STUDENT NAME")
The second column is grade (header "GRADE")
The third through 10th columns as teacher names ("P1Teach", "P2Teach", etc)

What I want to do is set up an array in a separate sheet that will return
all of the students for which P1Teach is a certain value (i.e. "MOORE").

Any assistance is helpful.

Excel 2007 PivotTable
Roster of students by Teach, Grade.
http://www.mediafire.com/file/fnmxzw...02_24_10a.xlsx