Absence Monitoring Help

**Lexx Diggler** · 09-09-2009, 12:23 PM

Hello People,

I have been asked to develop a spreadsheet into which absence data can be entered, and flagged up if the data meets following 3 criteria;

more than 3 occasions of absence in any rolling 3 month period
More than 2 weeks continuous absence
More than 6 weeks cumulative absence in any rolling 12 month period

For each month, there is a sheet, for example, "Month 1", and within each sheet there is a table containing employee specific info starting from column AI (name, DOB, sick pay entitlement etc), a column for each day of the month for entering an absence code, i.e. "S" for Sick etc, and a last column for flagging up the when one of the criteria has been met. Before Column AI is the previous months data

There is also a sheet summarizing the total sick days for the year by month, with the previous years data hidden away again before the emplyee info

So far i have managed to flag up the above using various IF type formula, but was asked if i could include a "dealt with" cell so all data prior to the "dealt with" date is ignored?!

we are using excel 2003 so cant have a cell for each day of the year, hence the separate sheets per month.

Im OK with formulas, but at using VBA im a novice.

Can anyone give me some advice as to how to achieve this flagging up / dealt with process?

Any help would be much appreciated.

Thanks in advance!

**royUK** · 09-10-2009, 03:20 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Lexx Diggler** · 09-10-2009, 05:08 PM

Hey thanks for the advice....

Heres a very basic example of what the main pages should look like to the user, what isnt factored in is the overlap of data which is required.

The actual sheet i have devloped to date exceeds the limit of the site (at 88mb!!!) but i can cut it down in size a bit if needed.

My main problem is the dealt with box, which should make the range change its start point somehow?...

As stated in the op, our company is currently licensed to use excel 2003, so i cant lap yearly data due to the horizontal limits.

would i be better transposing the whole thing? Although that would limit the number of employee references available...

Any advice would be much appreciated....

Thanks