Here's one formulas play to try, which will auto-extract into 3 separate
summary sheets by category:
Accomplishment, Meetings Attended, Training

Sample construct at:
http://www.savefile.com/files/6288396
Merge Data From Multiple Sheets_jjjam_wks.xls

In a sheet: Staff
List the staffs' sheetnames in A1 down:
Joe
Judy
Mark
etc

(names listed must match exactly what's on the tabs)

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 Add

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.

Continue to create another defined name
(a dynamic range) for the staffs' sheetnames

Put under "Names in workbook:": Staff
Put in the "Refers to:" box:
=OFFSET(Staff!$A$1,,,COUNTA(Staff!$A:$A))
Click OK

In a new sheet named: Accomplishment
Put in C1: =WSN
The above will extract the sheetname into C1
Just enter the labels in B1, D1 to complete:
Date, Hrs Worked

Put in A2:
=INDEX(Staff,INT((ROWS($A$1:A1)-1)/2)+1)

Put in B2, copy B2 to D2:
=OFFSET(INDIRECT("'"&$A2&"'!A1"),MOD(ROWS($A$1:A1)-1,2)+VLOOKUP($C$1,{"Accom
plishment",1;"Meetings Attended",4;"Training",7},2,0),COLUMN(A1)-1)

Select A2:D2, fill down by 2 x the number of staffs' sheets to be extracted.
For 20 staff, fill down by 2 x 20 = 40 rows to D39.
Format the date col to taste

Now, just make a copy of the sheet: Accomplishment,
rename the sheet as: Meetings Attended
and we'd get the results for "Meetings Attended"
(just spruce up the label in D1)

Repeat the copy rename sheet process
to extract the details for "Training"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jjjam" wrote in message
...
Imagine one Excel spreadsheet with 20 worksheets within; each worksheet
assigned to a team member; and each worksheet containing the monthly
accomplishments of that team member.

What I need to do is merge the text of each worksheet into a "summary"
worksheet, showing Joe's accomplishments, then Judy's, etc. etc.

I'm looking for any ideas how best to do this. Any help is GREATLY
appreciated.

Each worksheet is identical in format.
Row A B C
1 Date Accomplishment Hrs Worked
2
3
4 Date Meetings Attended Hrs Spent
5
6
7 Date Training Hrs
8
9