Well, this is just a suggestion, but ...

First make sure the sheet with the calendar has the name in the first column
and date in the second. Order the list alphabetically.
On the second sheet in the first cell next to the name type =VLOOKUP
(A1;Sheet1!A:B;2;false)
Now the formula explained: A1 means the cell where is the value we are
looking for. Sheet1 is the name of the sheet we are looking the data from. A:
B means the columns the data is looked from. "2" is the number of the column
where is the data we wish the function to show in the cell we are typing in
the formula. And "false" is the part of the function that is supposed to show
in case the search ends up with no value (eg no such name exist in the first
sheet).
Now, this formula ends up showing you the date behind the name. Into third
column you enter the date. Into the forth column type in the following
formula: =IF(A3=A2;"on vacation";""). This formula basically means the
following: if the date entered into A3 is the same as the one in A2, the cell
shows the text "on vacation" and in case those two cells do not match, the
cell shows up blank.

This VLOOKUP formula is something that you can hide from general view (before
drag the formula down to like row 20000 to have it working at all times).

Now you should know the basics, just see if it really works for you.


Rgrds,
Kristiina

ganga wrote:
Hi Kristina,

I really need your help with formula.

Thank you

I suppose first a "vlookup" for seeing if the name is behind some date (the
formula will also bring forward the date the name is behind) and an "if"
[quoted text clipped - 4 lines]
Rgrds,
Kristiina

--
I help with Excel and PowerPoint
Office ToDo
http://www.officetodo.com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/201005/1