Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
I'm trying to create a calendar vacation spreadsheet but I want to avoid
having certain department managers/supervisors from taking the same days off. Is there a way to create a spreadsheet that will help me do that? For instance, if I enter a date, and a manager already has that scheduled off, is there a formula that I can create that will give me a pop up message that says "date already taken" or something similar to that? Thanks in advance for any help. Josie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
Assume list of dates that are already taken is in A2:A367
Date to check is in B2. In cell C2, =IF(ISERROR(MATCH(B2,$A$2:$A$367,0),"","Date already taken" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jlcyear2004" wrote: I'm trying to create a calendar vacation spreadsheet but I want to avoid having certain department managers/supervisors from taking the same days off. Is there a way to create a spreadsheet that will help me do that? For instance, if I enter a date, and a manager already has that scheduled off, is there a formula that I can create that will give me a pop up message that says "date already taken" or something similar to that? Thanks in advance for any help. Josie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
More options:
#1 You can use conditional formatting to all the cells used for supervisor dates. Simply select the cell in question, goto format at top of page, select conditional formatting, then select not equal to, then pick the cell you do not want it to equal to, and finally a format if the date is again. This format you choose can hilight the cell a certain color but does not give a message. #2 Data Validation: Simply select the cell in question then goto data at the top of page then validation then choose not equal to then the cell you do not wish to repeat. this will not allow duplicated data to be entered. You can even put a message to display when someone tries to duplicate data. Good luck. "Jlcyear2004" wrote: I'm trying to create a calendar vacation spreadsheet but I want to avoid having certain department managers/supervisors from taking the same days off. Is there a way to create a spreadsheet that will help me do that? For instance, if I enter a date, and a manager already has that scheduled off, is there a formula that I can create that will give me a pop up message that says "date already taken" or something similar to that? Thanks in advance for any help. Josie |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
Thanks Luke, but I'm lost. Not an excel expert so I don't understand how to
create the function you suggested....Sorry about that! "Luke M" wrote: Assume list of dates that are already taken is in A2:A367 Date to check is in B2. In cell C2, =IF(ISERROR(MATCH(B2,$A$2:$A$367,0),"","Date already taken" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jlcyear2004" wrote: I'm trying to create a calendar vacation spreadsheet but I want to avoid having certain department managers/supervisors from taking the same days off. Is there a way to create a spreadsheet that will help me do that? For instance, if I enter a date, and a manager already has that scheduled off, is there a formula that I can create that will give me a pop up message that says "date already taken" or something similar to that? Thanks in advance for any help. Josie |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
You should have been able to just copy it from here and paste into cell C2.
But, breaking down the formula to explain: The MATCH part needs to contain first what cell you want to compare (the date you are entering) with the dates you already have (A2:A367). I picked 367 randomly, make it as large/small as you need. The "$" symbols are to make the array absolute. If you are not going to copy the formula/move the cells, you can leave them out. The "0" at the end of the formula tells Excel to find an exact match. Also allows you to enter dates randomly, not sorted. The rest of the formula is an IF formula. If there is an error with the MATCH, it means there is no date found, i.e., the date is okay. (display nothing) If it finds a match (no error) then display a warning. I'll post again: =IF(ISERROR(MATCH(B2,A2:A367,0),"","Date already taken" Hope that explains things. Remember, you can change B2 to whatever cell you are inputting a date into to check, and A2:A367 can be wherever you have a list of dates that you want blocked. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jlcyear2004" wrote: Thanks Luke, but I'm lost. Not an excel expert so I don't understand how to create the function you suggested....Sorry about that! "Luke M" wrote: Assume list of dates that are already taken is in A2:A367 Date to check is in B2. In cell C2, =IF(ISERROR(MATCH(B2,$A$2:$A$367,0),"","Date already taken" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jlcyear2004" wrote: I'm trying to create a calendar vacation spreadsheet but I want to avoid having certain department managers/supervisors from taking the same days off. Is there a way to create a spreadsheet that will help me do that? For instance, if I enter a date, and a manager already has that scheduled off, is there a formula that I can create that will give me a pop up message that says "date already taken" or something similar to that? Thanks in advance for any help. Josie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|