validation
in [General]
|
From: ganga on 7 May 2010 12:06 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
From: Kristiina on 7 May 2010 13:49 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 -- I help with Excel and PowerPoint Office ToDo http://www.officetodo.com
From: ganga on 7 May 2010 14:45 Hi Kristina, I really need your help with formula. Thank you "Kristiina" wrote: > 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 > > -- > I help with Excel and PowerPoint > Office ToDo > http://www.officetodo.com > > . >
From: Kristiina via OfficeKB.com on 8 May 2010 02:31 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.aspx/ms-excel/201005/1
|
Pages: 1 Prev: Dividing Worksheet into Two Columns Next: SUMPRODUCT again! |