list sheet 1 - same cell (eg A1) in a group of worksheets sheet

[Helen B]

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

 

[ShaneDevenshire]

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.

 

[Max]

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