validation

[ganga]

hi,

I have a workbook with 2 sheets. In one sheet I have a calender and other
sheet just list of names. what i want to do is: In the calender sheet if i
have a name under a date and when i enter that date beside that name in the
sheet of name list, i want to have a message saying that he/she is on
vacation.

For eg:

In calender sheet

a1&b1(merge 2 cells) (date-05-05-2010)
a2-ganga
b2-ravi

In name list sheet
a1-ganga- if i enter 05-05-2010 then the msg should say ganga is on vacation
a2-ravi-if i enter 05-05-2010 then the msg should say ravi is on vacation

Thank you

 

[Kristiina]

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"
formula to see if it matches with the date. If it matches, the formula will
set a text "on vacation".
If you need help with the formulas, just let me know

Rgrds,
Kristiina

 

[ganga]

Hi Kristina,

I really need your help with formula.

Thank you

 

[Kristiina via OfficeKB.com]

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

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.aspx/ms-excel/201005/1