Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
list sheet 1 - same cell (eg A1) in a group of worksheets sheet
I want to create a summary worksheet that shows the data in the same cells on
all the other worksheets in the workbook. I have 30 individual worksheets, one for each employee in the department, detailing overtime absence etc for the month. On sheet 1 I want to create a list of all employees and a total of their overtime etc. ie On each individual sheet = Employee name E5, overtime D45, sick J46 etc. On summary sheet a list of Employee name E5, overtime D45, sick J46 etc for each individual. I have tried "fill series" but there is no option for series = next worksheet. Hope this all makes sence to somebody. Helen |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
list sheet 1 - same cell (eg A1) in a group of worksheets sheet
Hi Helen,
Assume you want to sum cell J46 for all your sheets on a summary sheet cell J46. 1. Put the Summary sheet at one end or the other of all the Employee worksheets 2. Keep all the Employee worksheets contiguous (no non-employee sheets between the employee sheets) 3. Move to the Summary sheet and select the cell where you want the total, I am using cell J46. 4. In that cell type =SUM( 5. Click the sheet tab for the first employee, then click the cell J46 on that sheet (the formula will read something like =SUM(Shane!J46 6. Hold down the Shift key and click on sheet tab for the last employee and press Enter. The formula will look something like =SUM(Shane:Helen!J46) You can copy this formula as needed. -- Cheers, Shane Devenshire "Helen B" wrote: I want to create a summary worksheet that shows the data in the same cells on all the other worksheets in the workbook. I have 30 individual worksheets, one for each employee in the department, detailing overtime absence etc for the month. On sheet 1 I want to create a list of all employees and a total of their overtime etc. ie On each individual sheet = Employee name E5, overtime D45, sick J46 etc. On summary sheet a list of Employee name E5, overtime D45, sick J46 etc for each individual. I have tried "fill series" but there is no option for series = next worksheet. Hope this all makes sence to somebody. Helen |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
list sheet 1 - same cell (eg A1) in a group of worksheets sheet
One other angle to your post ...
If you have identically structured source sheets, you could use INDIRECT to easily "summarize" it on a separate sheet Here's an illustration tailored to your posting: http://www.freefilehosting.net/download/3bl31 Summarizing using indirect.xls In the summary sheet, List the sheetnames in A3 down. These must match exactly with what's on the tabs, except for case In B2 across, list the labels: Name, OverTime, Sick In B1 across, list the corresponding cell refs: E5, D45, J46 Place in B3: =IF(COUNTA($A3,B$1)<2,"",INDIRECT("'"&$A3&"'!"&B$1 )) Copy B3 across/fill down to populate the table as far as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Helen B" wrote: I want to create a summary worksheet that shows the data in the same cells on all the other worksheets in the workbook. I have 30 individual worksheets, one for each employee in the department, detailing overtime absence etc for the month. On sheet 1 I want to create a list of all employees and a total of their overtime etc. ie On each individual sheet = Employee name E5, overtime D45, sick J46 etc. On summary sheet a list of Employee name E5, overtime D45, sick J46 etc for each individual. I have tried "fill series" but there is no option for series = next worksheet. Hope this all makes sence to somebody. Helen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter Range on Sheet B Based on List on Sheet A | Excel Discussion (Misc queries) | |||
TAKING INFO FROM ONE SHEET AND PRODUCING A LIST IN ANOTHER SHEET | Excel Discussion (Misc queries) | |||
how do i list the name off all my worksheets on a sheet | Excel Worksheet Functions | |||
How do I group worksheets (Lotus 123 function is "Sheet>Group Shee | Excel Worksheet Functions | |||
Selection from list on main sheet from suplemental sheet in same w | New Users to Excel |