Merging data from mulitple worksheets

[Guest]

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

 

[JDB]

Use the =indirect() function. I use ti all the time

For example -name your tabs "John', "Joe", what ever

Then use Col A to hold the names of the tabs or another cell if you
want

Lets say you put "John" in A2

then this function will show cell B23 on Johns tab.

=INDIRECT($A2&"!b23")

Very powerful

HTH

JDB

 

[Max]

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 A22, 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"

 

[April]

How can I do the opposite. I have one large worksheet but I would like to
create one worksheet per a criteria of the table.

 

[Dave Peterson]

I'd look at these techniques:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb