Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging data from mulitple worksheets
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging data from mulitple worksheets
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 jjjam wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging data from mulitple worksheets
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging data from mulitple worksheets
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. "jjjam" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging data from mulitple worksheets
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 April wrote: 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. "jjjam" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfer data from multiple worksheets | Excel Discussion (Misc queries) | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
create one pivot from mulitple data sources | Excel Discussion (Misc queries) | |||
merging different worksheets in a workbook to one sheet | Excel Worksheet Functions | |||
data entry on multiple worksheets | Excel Discussion (Misc queries) |