Formula Assistance

[Guest]

I need to reduce the errors I'm making in typing information twice on a
spreadsheet to track employee days off. Each employee is allowed to have 2
personal days a year and I'm allowed to have no more than 5 employees off on
the same day. I have a spreadsheet with the dates of the year in column A,
starting in A3. The day of the week is in column B. In columns C-G I have
space to enter employees #1 - #5. In column K I have a list of all
employees, Column L & M shows the first and second personal day (in date
format). What I would like to do is enter the date an employee schedules a
personal day for in Columns C-G (depending upon how many have been requested)
and have that information automatically be entered into the appropriate
column in L or M by the employee name in column K.

Your assistance is appreciated.

 

[Jason Morin]

For simplicity's sake, let's assume you have dates in
A3:A21 and you are typing the names found in col. K into
columns C-G. Place this formula in L1, press ctrl + shift
+ enter, and fill down:

=INDEX($A$3:$A$21,MIN(IF(K1=$C$3:$G$21,ROW($C$3:$G$21)))-
MIN(ROW($C$3:$G$21))+1)

Place this one in K1, ctrl + shift + enter, and fill down:

=INDEX($A$3:$A$21,SMALL(IF(K1=$C$3:$G$21,ROW
($C$3:$G$21)),2)-MIN(ROW($C$3:$G$21))+1)

An error value means that an employee hasn't been
assigned 1 or both days off. You can hide the error
values by selecting columns L and K, changing the font to
white, then use custom formatting under Format > Cells >
Number tab with:

[Black]mm/dd/yy

Lastely, you'd probably want to flag if a person's name
has been entered for days off more than twice. To do
that, select C3:G21, go to Format > Conditional
Formatting, select "Formula Is" and put:

=COUNTIF($C$3:$G$21,INDIRECT("rc",0))>2

Press the Format button and format as desired. If you'd
like to have a sample workbook that demonstrates all
this, send me an e-mail tonight with the orignal post in
the body (change OPPOSITEOFCOLD to you know what) and
I'll email it tomorrow morning.

HTH
Jason
Atlanta, GA