How to generate in Excel a pay stub with a "Year to Date" column?

February 17, 2006 10:23 AM

I'm using Excel to create a weekly pay stub for a nanny, but I haven't figured out how to automatically include the previous week's data to create a "Year to Date" column for wages and taxes.

I'm using an Excel spreadsheet to create a pay stub for our nanny. On
the pay stub, from her hours her wages and taxes are calculated, and I
also have a "Year to Date" (YTD) column for the totals of wages and taxes
over the year.

Right now, in order to generate a new pay stub for each week, I create
a copy of blank pay stub template to a new Excel file, and copy the previous week's YTD
field values into a "Previous YTD" column, from which the current week's YTD
data is calculated.

I want to avoid having to manually do the copy of "Year to Date"
values from the previous week's pay stub, to the current week's
pay stub. Ideally, I'd like to just enter the week's date and hours, and a
pay stub will be generated including the YTD column. How can this be
done in Excel?

I thought of instead of using a new Excel file for each week's
pay stub, a new sheet for each week can be added to a single Excel file. But I didn't find
anyway to express a formula in Excel that says "Take the previous
sheet's value and put it here". It seems that formulas with sheets can
only include an absolute sheet number, and not a relative one.

Any ideas on how to do this?

Thanks.

posted by ShooBoo to Computers & Internet (6 answers total)

Instead of making a separate file for each week, make one file that has each week's data on one line.

You can make the second sheet of the document have a nicely laid out, printable pay stub that sucks the right data from one row of the first sheet and puts it where you want it.

At that point it will be trivial to do year-to-date calculations since everything will be nicely lined up in one column that you would want to calculate on.
posted by bcwinters at 10:53 AM on February 17, 2006

I would do the following:
Make one excel file and have each paycheck in a separate tab. Name the cell with the current period total something like, for example, "Current." Make a new paystub by copying the original tab, so that each tab has the same cell with the same name. Now make a tab to calculate the YTD. In this tab make a table that will draw in the total from each paystub. The columns of this table should be labeled with the name of each tab (Paycheck1, Paycheck2, or whatever). The row headers will be named whatever it is you named the relevant cells in the paystub stabs (tax, hours, wages, etc). To fill in the cell use this formula:

=IF(ISERROR(INDIRECT([cell reference of column header]&"!NameOfRelevantCell")),"NA", INDIRECT([cell reference of column header]&"!NameOfRelevantCell")

Then in each paystub tab, in the YTD cells, make a formula that equals the total for the whole year from the main tab. The future periods will show up as NA since those tabs haven't been created yet and won't break the formula.

Sorry if this is not very clear. If you want, email me at the address in my profile and I'll send you a spreadsheet (put your address as the subject, so I can email you back from work).
posted by mullacc at 10:59 AM on February 17, 2006

Use one file and 2 sheets. Use the first sheet for all of your data entry and also to store all of the nanny's bookeeping. Use the second sheet as a continuously updated pay stub. It should be a simple matter to set things up on the first sheet so that the needed data can be put in cells that can be referenced directly on the second sheet.
posted by JJ86 at 11:17 AM on February 17, 2006

When Mullacc says "Copy the Tab" he means, right clilck on the tab name (where it probably says "sheet 1" now), select Move or Copy, make sure the first drop box has your file name in it, select "Move to end" in the middle box, and then check the box that says "Make a copy."

I would have one sheet that is the cumulative information -- just a table that shows what you want (or just constantly reuse it as JJ says), and then a second sheet that's laid out like a paystub that you can reference back into the first page, actually. Then you can copy the paystub page as many times as you like while keeping a "running total" on the first sheet of the document.
posted by Medieval Maven at 1:43 PM on February 17, 2006

I worked it out with Excel to make it as elegant as possible(at least for me!). Basically I use a similar approach to bcwinters. On the first sheet set up all the weeks, you don't even have to complete everything to begin with. Make columns for your basic data; hours worked, pay rate, income, YTD income, etc. The set up some calc cells somewhere out of the way. In the first, use the MAXA function on your YTD column to find the last value entered. In the next, use MATCH to find the row. Then set up 3 cells to report the current cells to find: hours worked, income and YTD income. Use those addresses with an INDIRECT function to give the current values to place in the paystub.
posted by JJ86 at 1:57 PM on February 17, 2006

Oops, forgot to mention to use the ADDRESS function in the next to last step with the 3 cells.
posted by JJ86 at 2:00 PM on February 17, 2006