ExcelBanter - student directory

Here's another option, a non-array formulas play which can deliver the
desired results automatically ..

A sample construct is available at:
http://www.savefile.com/files/28968
AutoSlice data w phone to separate sht by teacher.xls

Assume the master list is in sheet named: Master
in cols A to D, headers in row1, data from row2 down
Col C = Teachers (eg: T1, T2, T3, etc), Col D = Phone#s (if any)

Using empty cols to the right of the data, say cols K onwards

List the teachers in K1 across, viz.: T1, T2, T3 .... Note that the listing
must be consistent with the values under col C

Put in K2:
=IF($D2="","",IF($C2=K$1,ROW(),""))
Copy K2 across and fill down to cover the max expected extent of source data

Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

[The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan]

In a new sheet named: T1
With the same col headers in A1:B1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(Master!$J:$J,,MATCH(WSN,M aster!$K$1:$IV$1,0)),ROWS($A$1:A1))),"",INDEX(Mast er!A:A,MATCH(SMALL(OFFSET(Master!$J:$J,,MATCH(WSN, Master!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(Master !$J:$J,,MATCH(WSN,Master!$K$1:$IV$1,0)),0)))

Copy A2 across to D2, fill down by the smallest extent sufficient to cover
all the cases expected for any one teacher, say down to D10

Cols A to B will return only the lines for the teacher: T1 from "Master"
with all lines neatly bunched at the top. Now, just make a copy of the sheet:
T1, rename it as: T2, and you'd get the results for T2. Repeat as required
for the rest of the teachers (a one-time job).
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Mark wrote:
I have a student directory that shows each kid by last name, first name,
teacher and phone number. It is sorted by last name. I need to make
seperate worksheets that places all kids (with phone numbers) under each
teacher in a list.

Any help will be appreciated.

Mark