I have a table as follows

A B C D
Start Date End Date Employee Vacation Type

I then need to create a calendar to how each day in the month and who is
available and who has taken a leave and which type of leave.

So, I need to search the table for each day of the month to find if it is in
the list or not, and if it is, to put the type of leave for THAT person in
the calendar view.

The calendar looks like this...

Date 1/1 1/2 1/3 1/4
Emp1
Emp2
Emp3

Try this...

The raw data table in the range A1:D5.

Calendar in the range A12:E15

Enter this array formula** in B13:

=INDEX($D$2:$D$5,MATCH(1,($C$2:$C$5=$A13)*($A$2:$A $5<=B$12)*($B$2:$B$5=B$12),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy across to E13 then down to B15:E15.

When the criteria are not met the formula will return #N/A. You can either
include an error trap in the formula to account for these which will make
the formula about twice as long or you can use conditional formatting to
"hide" the #N/A. I would recommend using CF to "hide" them. They'll still be
there, you just won't see them. Or, you can just leave them. Post back and
advise on how you'd like to handle that.

--
Biff
Microsoft Excel MVP


"Hany ElKady" wrote in message
...
I have a table as follows

A B C D
Start Date End Date Employee Vacation Type

I then need to create a calendar to how each day in the month and who is
available and who has taken a leave and which type of leave.

So, I need to search the table for each day of the month to find if it is
in
the list or not, and if it is, to put the type of leave for THAT person in
the calendar view.

The calendar looks like this...

Date 1/1 1/2 1/3 1/4
Emp1
Emp2
Emp3

Hi Biff,

Thanks I seem to had been going around in circules with a lot of IF
statements, this is much easier. Just one last thing, the CF, I can't seem to
be able to hide the error #N/A, is there a way to tell it to HIDE the
content, because I had formatted this table using the standard table
formating in excel, and each row is a different color, plus I already have
conditional formatting to show weekends and holidays.

Thanks
Hany
--
Hany ElKady
Professional Services Architect
Technology & Service Delivery


"T. Valko" wrote:

Try this...

The raw data table in the range A1:D5.

Calendar in the range A12:E15

Enter this array formula** in B13:

=INDEX($D$2:$D$5,MATCH(1,($C$2:$C$5=$A13)*($A$2:$A $5<=B$12)*($B$2:$B$5=B$12),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy across to E13 then down to B15:E15.

When the criteria are not met the formula will return #N/A. You can either
include an error trap in the formula to account for these which will make
the formula about twice as long or you can use conditional formatting to
"hide" the #N/A. I would recommend using CF to "hide" them. They'll still be
there, you just won't see them. Or, you can just leave them. Post back and
advise on how you'd like to handle that.

--
Biff
Microsoft Excel MVP


"Hany ElKady" wrote in message
...
I have a table as follows

A B C D
Start Date End Date Employee Vacation Type

I then need to create a calendar to how each day in the month and who is
available and who has taken a leave and which type of leave.

So, I need to search the table for each day of the month to find if it is
in
the list or not, and if it is, to put the type of leave for THAT person in
the calendar view.

The calendar looks like this...

Date 1/1 1/2 1/3 1/4
Emp1
Emp2
Emp3

Well, if you can add a condition to the CF or incorporate this into an
existing condition, what you want to do is test the cell for an error and
set the text color to be the same as the background color thus making the
text (or the #N/A error) unseen.

=ISERROR(A1)

If you can't do that then you'll need to use an error trap in the formula:

=IF(ISNA(MATCH(1,($C$2:$C$5=$A13)*($A$2:$A$5<=B$12 )*($B$2:$B$5=B$12),0)),"",INDEX($D$2:$D$5,MATCH(1 ,($C$2:$C$5=$A13)*($A$2:$A$5<=B$12)*($B$2:$B$5=B$ 12),0)))


--
Biff
Microsoft Excel MVP


"Hany ElKady" wrote in message
...
Hi Biff,

Thanks I seem to had been going around in circules with a lot of IF
statements, this is much easier. Just one last thing, the CF, I can't seem
to
be able to hide the error #N/A, is there a way to tell it to HIDE the
content, because I had formatted this table using the standard table
formating in excel, and each row is a different color, plus I already have
conditional formatting to show weekends and holidays.

Thanks
Hany
--
Hany ElKady
Professional Services Architect
Technology & Service Delivery


"T. Valko" wrote:

Try this...

The raw data table in the range A1:D5.

Calendar in the range A12:E15

Enter this array formula** in B13:

=INDEX($D$2:$D$5,MATCH(1,($C$2:$C$5=$A13)*($A$2:$A $5<=B$12)*($B$2:$B$5=B$12),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy across to E13 then down to B15:E15.

When the criteria are not met the formula will return #N/A. You can
either
include an error trap in the formula to account for these which will make
the formula about twice as long or you can use conditional formatting to
"hide" the #N/A. I would recommend using CF to "hide" them. They'll still
be
there, you just won't see them. Or, you can just leave them. Post back
and
advise on how you'd like to handle that.

--
Biff
Microsoft Excel MVP


"Hany ElKady" wrote in message
...
I have a table as follows

A B C D
Start Date End Date Employee Vacation Type

I then need to create a calendar to how each day in the month and who
is
available and who has taken a leave and which type of leave.

So, I need to search the table for each day of the month to find if it
is
in
the list or not, and if it is, to put the type of leave for THAT person
in
the calendar view.

The calendar looks like this...

Date 1/1 1/2 1/3 1/4
Emp1
Emp2
Emp3

Hi Biff, thanks for all your help.

I tried your formula, and then did a bit of tweaking myself... I actually
got a much shoter formula by using iferror instead of isna :)

Tanks
--
Hany ElKady
IT Consultant

"T. Valko" wrote:

Well, if you can add a condition to the CF or incorporate this into an
existing condition, what you want to do is test the cell for an error and
set the text color to be the same as the background color thus making the
text (or the #N/A error) unseen.

=ISERROR(A1)

If you can't do that then you'll need to use an error trap in the formula:

=IF(ISNA(MATCH(1,($C$2:$C$5=$A13)*($A$2:$A$5<=B$12 )*($B$2:$B$5=B$12),0)),"",INDEX($D$2:$D$5,MATCH(1 ,($C$2:$C$5=$A13)*($A$2:$A$5<=B$12)*($B$2:$B$5=B$ 12),0)))


--
Biff
Microsoft Excel MVP


"Hany ElKady" wrote in message
...
Hi Biff,

Thanks I seem to had been going around in circules with a lot of IF
statements, this is much easier. Just one last thing, the CF, I can't seem
to
be able to hide the error #N/A, is there a way to tell it to HIDE the
content, because I had formatted this table using the standard table
formating in excel, and each row is a different color, plus I already have
conditional formatting to show weekends and holidays.

Thanks
Hany
--
Hany ElKady
Professional Services Architect
Technology & Service Delivery


"T. Valko" wrote:

Try this...

The raw data table in the range A1:D5.

Calendar in the range A12:E15

Enter this array formula** in B13:

=INDEX($D$2:$D$5,MATCH(1,($C$2:$C$5=$A13)*($A$2:$A $5<=B$12)*($B$2:$B$5=B$12),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy across to E13 then down to B15:E15.

When the criteria are not met the formula will return #N/A. You can
either
include an error trap in the formula to account for these which will make
the formula about twice as long or you can use conditional formatting to
"hide" the #N/A. I would recommend using CF to "hide" them. They'll still
be
there, you just won't see them. Or, you can just leave them. Post back
and
advise on how you'd like to handle that.

--
Biff
Microsoft Excel MVP


"Hany ElKady" wrote in message
...
I have a table as follows

A B C D
Start Date End Date Employee Vacation Type

I then need to create a calendar to how each day in the month and who
is
available and who has taken a leave and which type of leave.

So, I need to search the table for each day of the month to find if it
is
in
the list or not, and if it is, to put the type of leave for THAT person
in
the calendar view.

The calendar looks like this...

Date 1/1 1/2 1/3 1/4
Emp1
Emp2
Emp3

using iferror instead of isna :)

Yeah, one of the advantages of using Excel 2007.

Thanks for the feedback!


--
Biff
Microsoft Excel MVP


"Hany ElKady" wrote in message
...
Hi Biff, thanks for all your help.

I tried your formula, and then did a bit of tweaking myself... I actually
got a much shoter formula by using iferror instead of isna :)

Tanks
--
Hany ElKady
IT Consultant

"T. Valko" wrote:

Well, if you can add a condition to the CF or incorporate this into an
existing condition, what you want to do is test the cell for an error and
set the text color to be the same as the background color thus making the
text (or the #N/A error) unseen.

=ISERROR(A1)

If you can't do that then you'll need to use an error trap in the
formula:

=IF(ISNA(MATCH(1,($C$2:$C$5=$A13)*($A$2:$A$5<=B$12 )*($B$2:$B$5=B$12),0)),"",INDEX($D$2:$D$5,MATCH(1 ,($C$2:$C$5=$A13)*($A$2:$A$5<=B$12)*($B$2:$B$5=B$ 12),0)))


--
Biff
Microsoft Excel MVP


"Hany ElKady" wrote in message
...
Hi Biff,

Thanks I seem to had been going around in circules with a lot of IF
statements, this is much easier. Just one last thing, the CF, I can't
seem
to
be able to hide the error #N/A, is there a way to tell it to HIDE the
content, because I had formatted this table using the standard table
formating in excel, and each row is a different color, plus I already
have
conditional formatting to show weekends and holidays.

Thanks
Hany
--
Hany ElKady
Professional Services Architect
Technology & Service Delivery


"T. Valko" wrote:

Try this...

The raw data table in the range A1:D5.

Calendar in the range A12:E15

Enter this array formula** in B13:

=INDEX($D$2:$D$5,MATCH(1,($C$2:$C$5=$A13)*($A$2:$A $5<=B$12)*($B$2:$B$5=B$12),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy across to E13 then down to B15:E15.

When the criteria are not met the formula will return #N/A. You can
either
include an error trap in the formula to account for these which will
make
the formula about twice as long or you can use conditional formatting
to
"hide" the #N/A. I would recommend using CF to "hide" them. They'll
still
be
there, you just won't see them. Or, you can just leave them. Post back
and
advise on how you'd like to handle that.

--
Biff
Microsoft Excel MVP


"Hany ElKady" wrote in message
...
I have a table as follows

A B C D
Start Date End Date Employee Vacation Type

I then need to create a calendar to how each day in the month and
who
is
available and who has taken a leave and which type of leave.

So, I need to search the table for each day of the month to find if
it
is
in
the list or not, and if it is, to put the type of leave for THAT
person
in
the calendar view.

The calendar looks like this...

Date 1/1 1/2 1/3 1/4
Emp1
Emp2
Emp3