copy data from workbook to workbook

[Robert N]

Need help with following problem.
I need to copy/link data from one workbook to another eg:
Master has 375 rows of data each row has 4 col
Job.....Student Name.....Supervisor.....School
This data is sorted by School then by Student Name
I want to grab all rows where column called School = DTS then copy o
link this data and display it in a new workbook called DTS.
Then I would want to do the same for all other schools again displayin
only the data from the 375 rows.
It would be great to have the data automatically update in workbook DT
if the data in Master changed. In my thinking it should be somethin
like this:

From workbook DTS lookup a range of rows and columns in MASTER I
column School = "DTS" grab the row or rows and paste or link t
workbook DTS.

Can I do this in exce

 

[Max]

Perhaps you might be happy enough
with this example set-up which approximates
your reqts but keeps everything within 1 book (tidier ..)

In Sheet1
-------------
Assume the "master" table is in cols A to D
data from row2 down

Job Student Supervisor School
Job1 Student1 Supervisor1 DTS
Job2 Student2 Supervisor2 FCG
Job3 Student3 Supervisor3 DTS
etc

List across in F1:G1,
the schools: DTS, FCG

Put in F2: =IF($D2="","",IF($D2=F$1,ROW(),""))

Copy F1 across to G2, then fill down by a safe "max"
expected number of rows of data in cols A to D,
say down to G1000

In a new Sheet2
-----------------------
Let's reserve cell A1 for the school name
Input in A1: DTS

Put the same col headers into A22, viz.:
Job Student Supervisor School

Put in A3:

=IF(ISERROR(MATCH(SMALL(INDIRECT("Sheet1!$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+
64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)),ROW(A1)),INDIRECT("Sheet1!$"&C
HAR(MATCH($A$1,Sheet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64))
,0)),"",OFFSET(Sheet1!$A$1,MATCH(SMALL(INDIRECT("Sheet1!$"&CHAR(MATCH($A$1,S
heet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)),ROW(A1)),INDIRE
CT("Sheet1!$"&CHAR(MATCH($A$1,Sheet1!$1:$1,0)+64)&":$"&CHAR(MATCH($A$1,Sheet
1!$1:$1,0)+64)),0)-1,COLUMN(A1)-1))

Copy A3 across to D3, then fill down by as many rows
as was done in Sheet1's cols F and G, say down to D1000

You'll see that cols A to D (in row3 down)
will auto-return the "filtered" rows from Sheet1
for the school: DTS input in A1,
i.e. for the sample data-set above:

Job Student Supervisor School
Job1 Student1 Supervisor1 DTS
Job3 Student3 Supervisor3 DTS

Now just duplicate/make a copy of Sheet2,
change the input in A1 in the new sheet to: FCG
and you'll get the "filtered" rows for FCG:

Job Student Supervisor School
Job2 Student2 Supervisor2 FCG

And if you re-label the default sheetnames
with the school names: DTS, FCG

you could also put in cell A1:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

which will auto-extract the sheetname into cell A1
(instead of having to input into cell A1)

Extend to suit ..