Excel

[Jlcyear2004]

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

 

[Luke M]

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"

 

[Jlcyear2004]

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!

 

[evoxfan]

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.

 

[Luke M]

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.