Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Formula Assistance
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. |
#2
|
|||
|
|||
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 -----Original Message----- 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Formula assistance required!!! | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
I need assistance with wrting an Excel formula | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions |