How do I create a total sheet

[Tia]

I've got a workbook that contains a payroll worksheet for each employee (9).
I would like to have one sheet that takes information from each sheet and
then provides a total. For example, I want the total amount paid out for a
payroll period for all the employees listed on my total sheet.

Each Sheet looks like this:
Payroll Commission Auto Allowance
08/31/07 1500.00 0.00 0.00
09/15/07 1500.00 200.00 600.00


I want a sheet that combines the information for all employees and provides
totals to look like this so I can have totals for each pay period:

Payroll Commission Auto Allowance
08/31/07 20000.00 0.00 0.00
09/15/07 20000.00 1000.00 10000.00

I know how to do it if the equation always referred to the same cell,
however, every pay period will add a new line of information and I will need
the current payperiod information.

Thanks for any suggestions!!

 

[Balan]

Hi Tia,
Did you try a pivot table ? It should solve your problem. If you want only
the summary of latest payroll and does not want the summary of the previous
payrolls, you may have to write a programme if you have large number of
sheets. But I understand you have only 9 sheets; if so, it may not be
difficult to write some formula using index and match functions. But I first
suggest that you try the pivot table - what if the summary position of all
the payroll periods are given; will it not be useful sometimes ?

If you are planning to try a creating a pivot table, ensure that you select
the option " Multiple consolidation ranges" under step 1 ("where is the data
that you want to analyze"). If you are new to pivot tables, this you can
access via Data Menu ( select Pivot Table and Pivot Chart Report"). In step 1
also select "Pivot Table" which will be a default option. While selecting
the ranges of data ( with column captions) do not forget to add one blank row
after the last record, so that whenever a new payroll data is added you can
insert a row and add the data which will be within the selected range for the
pivot table.

Best Wishes

 

[Balan]

Tia,

I am sorry, I forgot to mention one more thing. Whenever you add new data (
using the insert row in the respective sheet) , you can update the pivot
table by taking the cursor to one of the cells of the pivot table and
clicking on the Refresh button ( ! mark) seen on the pivot table toolbar
which automatically appear when ever you open the sheet with the pivot table.
This will ensure that the summary of the new payroll period is added to the
table.

Balan