How do I keep each week's data from the payroll calcualtor?

[Guest]

I have Excel 2002 and I am using the Payroll Calulator template that I
downloaded from office.microsoft.com. It's great for plugging in an
employee's total hours for the week and instantly all the taxes etc. are
calculated and a payroll stub is ready for printing. The problem is there is
no permanent info. It only functions as a calculator, so each week when I
plug in the new hours, all the previous data is replaced by the newly
calulated data. What I would like to be able to do is keep the data for each
week on a separate sheet for each employee: week one's data on line one for
that employee, week two's data on line two, etc. Does anyone know how I
could do this? Thanks for any help!! Betsy

 

[Art Farrell]

Hi Betsy,

You could set up individual sheets and manipulate the data manually, or it
could be done with a macro to remove the drudgery. Here is an alternate to
do it without a macro and not too much manual input once you set it up.It
will keep all your weekly data on one sheet where you can sort it by
employee or Autofilter it to see just one employee at a time.

1- Right click on your Payroll sheet.Select Move or Copy. Then click in
'Create a copy' and OK.

2- Right click on this tab and choose Rename. Type in a name for the sheet,
such as, 'Weekly Summary'.

3- Click on the top left intersection of this sheet and choose 'Copy'. Then
select Edit > Paste Special > Values. This will eliminate the formulas
which were carried over.

4- Make a new column at the end of your data called 'Period Ending'. Copy
the date of the period and select the cells opposite the employees and paste
the date.

5- If you have rows above your headings you can eliminate them.

6- For succeeding weeks just copy the data and select the next row on the
'Weekly Summary' sheet and Paste Special > Values. Then copy that date to
the last column for those entries.

This way you will have all your employee's data for 52 weeks on one sheet
and the data can be sorted by employee and date, or if you just want to see
one employee you can Autofilter.

If you have questions or want a macro email me.

CHORDially,
Art Farrell