Simple question, badly explianed

thesproing

Board Regular
Joined
Jul 16, 2007
Messages
76
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.
 
Upvote 0

Forum statistics

Threads
1,190,558
Messages
5,981,690
Members
439,730
Latest member
gjvv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top