Simple question, badly explianed

[thesproing]

Hi,

This is going to take some explaining but i will persever to make it as consise as possible, any help would be greatly appriciated!!!

Ok, so basically im trying to make a working table to trace employee sickness, month by month for the period of a year at a time, to show the percentage of time they have take off that year.

The table contains the employee names, the year and the subsequent months underneath, and the number of hours taken sick show in the relevant cells.

However, this is where it gets tricky...on a seperate sheet i have entered the data for three seperate years, 2005 - 07 inclusive, and the original table changes depending on what year you need to view. So far no problems.

The thing is that not all the staff started at the same time, ie they began in different months, so to show an accurtate percentage of time taken off sick it needs to be calculated specific to the indervidual, eg from march 05 to march 06.

This is easy enough to do with individual formulae, but staff are constantly coming and going, so i want the all the staff details, such as start date, and contracted hours to be stored on a seperate table, and a generic formulare to work off of that table, use the data contained and work out the average percentage of time taken off each year for whatever staff are in that particular table.

Further, new data will be added monthly, and i would like the figures to be as up to date as possible. So what i would really really like is for excell to realise when each individual completes a another "full year" of employment and shows the most upto date representation of the time they have taken off to sickness.

As way of an example, if an employee starts in Sept 2005, then the figures produced in July 2007 would be for the year Sept 2005 - Sept 2006. However, as soon as the figures for Sept 2007 are filled in i would like the percentage of time taken to show Sept 2006 - Sept 2007.

I know this is very long winded, but im sure it can be done, and i could prob do it with a lot of spagetti programing, but there must be an easier way! Any advice would be amazing,

Thanks,

Olly

 

[BrianB]

The beginning of a project such as this is really a very simple matter. Just start a worksheet table with column headings in row 1 and data underneath. Can always add columns later on. This becomes a database that can be used for all kinds of analysis or extracting subsets of data. Excel has numerous data functions (see the Data menu) that do this very fast.

The more you use separate sheets, the worse things become.

Just keep adding new data to the bottom. Repeating things like employee names. It is necessary to make sure that things like employee names are spelled the same (presumably you have employee numbers ?) otherwise this will not work. Each column needs to contain consistent data.