I work in a hospital and I need to make doctor "scorecards". I have a main
spreadsheet in my excel file where all the doctor and case data is saved for
the previous month. This data includes the name of the doctor, the date, and
if the case was late, among other things. What I need is for a formula or
program or something to allow me to run it and have excel automatically copy
all the data on a row for a specific doctor to a different excel spreadsheet.
For example, if Dr. John Doe did 7 surgeries this month, i need excel to
sort through the data on the main worksheet and paste the data for only his 7
spreadsheets to a tab that is labeled with his name (so I can see how he did
for this month). Is this possible with excel? I have searched everywhere
and can't seem to find what I'm looking for. I'm sorry if my explanation
isn't clear. If you need me to try to explain it better or give you a sample
excel file just let me know.

Thanks so much,
Matt

I made a typo earlier....

For example, if Dr. John Doe did 7 surgeries this month, i need excel to
sort through the data on the main worksheet and paste the data for only his 7
spreadsheets to a tab that is labeled with his name (so I can see how he did
for this month).

It should say "for only his 7 surgeries"

Sorry....

Here's a play which automates it using non-array formulas ..

A sample construct is available at:
http://www.savefile.com/files/2544973
Auto-Extract Case Lines to Own Sheet by Doc Name.xls

In a sheet: Z (the "master" sheet containing all listings)
Assume data is in cols A to E, data in row2 down,
with the key col = col B, which contains the names of the doctors
(eg: John Doe, Peter Long, George Pan)

Using empty cols to the right,
List the 3 doctor names across in K1:M1

Put in K2: =IF($B2=K$1,ROW(),"")
Copy K2 across to M2, fill down to say, M20
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 taken from
a post by Harlan Grove.)

In a new sheet named: John Doe
With the same col headers pasted into A1:E1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(Z!$J:$J,,MATCH(WSN,Z!$K$1 :$M$1,0)),ROWS($A$1:A1))),"",INDEX(Z!A:A,MATCH(SMA LL(OFFSET(Z!$J:$J,,MATCH(WSN,Z!$K$1:$M$1,0)),ROWS( $A$1:A1)),OFFSET(Z!$J:$J,,MATCH(WSN,Z!$K$1:$M$1,0) ),0)))

Copy A2 across to E2, fill down to say, E10
(copy down by the smallest possible range sufficient
to cover the max expected extent for any doctor.
Here, I've assumed that 9 rows (rows 2 to 10) is sufficient)

Cols A to E will return only the lines for doctor: John Doe from Z,
with all lines neatly bunched at the top

Now just make a copy of the sheet: John Doe,
rename it as the next doctor: Peter Long,
and we'd get the results for that doctor

Repeat the copy rename sheet process
to get the 3rd doctor: George Pan. Adapt to suit ..

Ensure that the names on the sheet tabs: John Doe, Peter Long, George Pan
match exactly* with the names listed in col B in Z,
and with the names listed across in K1:M1 in Z.
*except for case

Watch out for any inconsistencies, typos, extraneous white spaces, etc.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Matt" wrote:
I made a typo earlier....
For example, if Dr. John Doe did 7 surgeries this month, i need excel to
sort through the data on the main worksheet and paste the data for only his 7
spreadsheets to a tab that is labeled with his name (so I can see how he did
for this month).

I work in a hospital and I need to make doctor "scorecards". I have a main
spreadsheet in my excel file where all the doctor and case data is saved for
the previous month. This data includes the name of the doctor, the date, and
if the case was late, among other things. What I need is for a formula or
program or something to allow me to run it and have excel automatically copy
all the data on a row for a specific doctor to a different excel spreadsheet.
For example, if Dr. John Doe did 7 surgeries this month, i need excel to
sort through the data on the main worksheet and paste the data for only his 7
spreadsheets to a tab that is labeled with his name (so I can see how he did
for this month). Is this possible with excel? I have searched everywhere
and can't seem to find what I'm looking for. I'm sorry if my explanation
isn't clear. If you need me to try to explain it better or give you a sample
excel file just let me know.

Thanks so much,
Matt

Thanks,

That worked perfectly and I really appreciate it.

Matt

"Max" wrote:

Here's a play which automates it using non-array formulas ..

A sample construct is available at:
http://www.savefile.com/files/2544973
Auto-Extract Case Lines to Own Sheet by Doc Name.xls

In a sheet: Z (the "master" sheet containing all listings)
Assume data is in cols A to E, data in row2 down,
with the key col = col B, which contains the names of the doctors
(eg: John Doe, Peter Long, George Pan)

Using empty cols to the right,
List the 3 doctor names across in K1:M1

Put in K2: =IF($B2=K$1,ROW(),"")
Copy K2 across to M2, fill down to say, M20
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 taken from
a post by Harlan Grove.)

In a new sheet named: John Doe
With the same col headers pasted into A1:E1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(Z!$J:$J,,MATCH(WSN,Z!$K$1 :$M$1,0)),ROWS($A$1:A1))),"",INDEX(Z!A:A,MATCH(SMA LL(OFFSET(Z!$J:$J,,MATCH(WSN,Z!$K$1:$M$1,0)),ROWS( $A$1:A1)),OFFSET(Z!$J:$J,,MATCH(WSN,Z!$K$1:$M$1,0) ),0)))

Copy A2 across to E2, fill down to say, E10
(copy down by the smallest possible range sufficient
to cover the max expected extent for any doctor.
Here, I've assumed that 9 rows (rows 2 to 10) is sufficient)

Cols A to E will return only the lines for doctor: John Doe from Z,
with all lines neatly bunched at the top

Now just make a copy of the sheet: John Doe,
rename it as the next doctor: Peter Long,
and we'd get the results for that doctor

Repeat the copy rename sheet process
to get the 3rd doctor: George Pan. Adapt to suit ..

Ensure that the names on the sheet tabs: John Doe, Peter Long, George Pan
match exactly* with the names listed in col B in Z,
and with the names listed across in K1:M1 in Z.
*except for case

Watch out for any inconsistencies, typos, extraneous white spaces, etc.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Matt" wrote:
I made a typo earlier....
For example, if Dr. John Doe did 7 surgeries this month, i need excel to
sort through the data on the main worksheet and paste the data for only his 7
spreadsheets to a tab that is labeled with his name (so I can see how he did
for this month).

I work in a hospital and I need to make doctor "scorecards". I have a main
spreadsheet in my excel file where all the doctor and case data is saved for
the previous month. This data includes the name of the doctor, the date, and
if the case was late, among other things. What I need is for a formula or
program or something to allow me to run it and have excel automatically copy
all the data on a row for a specific doctor to a different excel spreadsheet.
For example, if Dr. John Doe did 7 surgeries this month, i need excel to
sort through the data on the main worksheet and paste the data for only his 7
spreadsheets to a tab that is labeled with his name (so I can see how he did
for this month). Is this possible with excel? I have searched everywhere
and can't seem to find what I'm looking for. I'm sorry if my explanation
isn't clear. If you need me to try to explain it better or give you a sample
excel file just let me know.

Thanks so much,
Matt

Glad to hear that, Matt.
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Matt" wrote:
Thanks,
That worked perfectly and I really appreciate it.