Holiday Planner

Morning All,
I have been asked to see if i can set up a holiday planner for our department
So far so good set a grid up for 2005 and conditionally formatted the cells
to change colour if an H - Holiday, S - Sickness, T- Training, O for Offsite,
M - Meeting appear. Then I have used countif to calculate the totals for each
Quarter for each individual. Not a problem so far. However it was then
pointed out to me that there are different leave start dates throughout the
year for each person.
At the moment I am using VLOOKUP to find the relevant info for each member
of staff for each quarter and keep it all on one sheet. thus the problem is
that Person A's leave could run from 1/6/2004 to 31/5/2005, but person B's
leave is 1/1/2005 to 31/12/2005. I can see myself getting into a complete
mess with different range lookups for each individual.
Has anyone got any ideas how I can overcome this problem of different leave
start dates and yet still keep the info to one page ?
TIA
Ajay

Could you insert a column in your look up table to give
the date the holiday year starts for each individual. Then
in your planner, you should be able to add another, say,
25 day leave entitlement to a person on
their "anniversary".
>-----Original Message-----
>Morning All,
>I have been asked to see if i can set up a holiday
planner for our department
>So far so good set a grid up for 2005 and conditionally
formatted the cells
>to change colour if an H - Holiday, S - Sickness, T-
Training, O for Offsite,
>M - Meeting appear. Then I have used countif to calculate
the totals for each
>Quarter for each individual. Not a problem so far.
However it was then
>pointed out to me that there are different leave start
dates throughout the
>year for each person.
>At the moment I am using VLOOKUP to find the relevant
info for each member
>of staff for each quarter and keep it all on one sheet.
thus the problem is
>that Person A's leave could run from 1/6/2004 to
31/5/2005, but person B's
>leave is 1/1/2005 to 31/12/2005. I can see myself getting
into a complete
>mess with different range lookups for each individual.
>Has anyone got any ideas how I can overcome this problem
of different leave
>start dates and yet still keep the info to one page ?
>TIA
>Ajay
>.
>

Thanks bobf will see if I can work this into the table
Cheers
Ajay

"bobf" wrote:

> Could you insert a column in your look up table to give
> the date the holiday year starts for each individual. Then
> in your planner, you should be able to add another, say,
> 25 day leave entitlement to a person on
> their "anniversary".
> >-----Original Message-----
> >Morning All,
> >I have been asked to see if i can set up a holiday
> planner for our department
> >So far so good set a grid up for 2005 and conditionally
> formatted the cells
> >to change colour if an H - Holiday, S - Sickness, T-
> Training, O for Offsite,
> >M - Meeting appear. Then I have used countif to calculate
> the totals for each
> >Quarter for each individual. Not a problem so far.
> However it was then
> >pointed out to me that there are different leave start
> dates throughout the
> >year for each person.
> >At the moment I am using VLOOKUP to find the relevant
> info for each member
> >of staff for each quarter and keep it all on one sheet.
> thus the problem is
> >that Person A's leave could run from 1/6/2004 to
> 31/5/2005, but person B's
> >leave is 1/1/2005 to 31/12/2005. I can see myself getting
> into a complete
> >mess with different range lookups for each individual.
> >Has anyone got any ideas how I can overcome this problem
> of different leave
> >start dates and yet still keep the info to one page ?
> >TIA
> >Ajay
> >.
> >
>