Sick and Vacation spreadsheet question

[latwell]

Hello! I'm new to the website, but have found many great answer here also. I am hoping someone might be able to provide me with some insight.


I have been creating an excel spreadsheet for a client to track used sick and vacation days. At the end of the year, they carry over any unused sick and vacation days. There is a summary sheet that shows the prior year vacation that is to be carried over (starting with 2011), followed by the annual addition, less the days used and followed by the days remaining.

The spreadsheet I have is great, but only for the current year. When 2012 my client will need to carry over the 2012 unused totals to 2013.

On a seperate page, I was wondering if I can list all the years like so:
2011
2012
2013
2014
.. and so on... with a formula next to it that would have the remaining vacation days left that are used for the following year.


Basically, I am trying to make it so that my client doesn't have to type in the carryover days to lower the rate of input errors. (The client had carried over the wrong number of days because they were handwriting items before and she shorted someone 17 sick days).

Thank you in advance for the help!

 

[guerillaunit]

Can you post some sample data of your sheets?

 

[latwell]

Data includes:

A tab for each employee. The individual employees tab includes:
<TABLE style="WIDTH: 94pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=125><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><TBODY><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 9.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl101 height=13 width=125 colSpan=2>January Days Used</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 height=13>Sick</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 align=right>0</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 height=13>Vacation</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 align=right>0</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 height=13>Personal</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 align=right>0</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl100 height=13>Floater</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 align=right>0</TD></TR></TBODY></TABLE>
There is a calander next to the table that the human resources person inputs the day off and the table above totals the items for the month.

On another page for the employee, there is a summary. The summary looks something like:

2011 Vacation Days Carryover 24 days
Vacation days accural 15 days
^^ The year changes automatically when the calendar year is changed in another section of the excel file.


Below is a table that is a Year to date summary for 2012:
<TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=202><COLGROUP><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 104pt; HEIGHT: 9.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=13 width=138>PY Vacation, Carryover</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 width=64 align=right>24</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=13>Yearly accrual</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 align=right>15</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=13>Total Vacation</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 align=right>39</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=13>Days Used</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 align=right>0</TD></TR><TR style="HEIGHT: 9.75pt; mso-height-source: userset" height=13><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 9.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl98 height=13>Days Remaining</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl99 align=right>39</TD></TR></TBODY></TABLE>
^^ I want to take the "days remaining" for 2012 and have them carry over when we go to 2013.


Pretty much the only place the client really has to enter anything is the calendar. There is another sheet that has the calendar year, if the client types in a specific year, the calendars change to reflect the correct dates in correspondence to the day.


Now that I'm thinking about it more thuroughly, I don't really believe I will be able to accomplish what I am thinking of haha.

 

[guerillaunit]

Still having trouble grasping what the layout of the workbook is. Can you upload the workbook (with sample data) on a fileshare site and post the link?

 

[latwell]

https://skydrive.live.com/view.aspx...racker.xlsx?cid=19da497782c03625&sc=documents<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>


Let me know if this does not work.

 

[guerillaunit]

Can you upload it to a website that doesn't require registration?

 

[latwell]

http://www.mediafire.com/?g4q3d3uf32anf8o


Ok, lets try this instead. Thank you for your patience with me.