Steve,
Here is one approach that uses some formulae and data validation (drop downs) to make shift assignments.
Assuming you have just two categories of workers, ie. 'Cooks' and 'Other'
Make two lists, one for 'Cooks', the 2nd for 'Other'. I did that in columns L and N.
Sheet1
| | K | L | M | N | O |
| 1 | #Shifts | Cooks | | Other | #Shifts |
| 2 | 2 | Bob | | Lenny | 3 |
| 3 | 1 | George | | Mark | 3 |
| 4 | 1 | Les | | Marty | 2 |
| 5 | 1 | Sam | | Mike | 2 |
| 6 | 1 | Steve | | Sebough | 3 |
| 7 | | | | Terry | 3 |
| 8 | | | | | |
<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>
| Spreadsheet Formulas |
| Cell | Formula | | K2 | =COUNTIF($B$2:$I$8,"*"&$L2&"*") | | O2 | =COUNTIF($B$2:$I$8,"*"&$N2&"*") | | K3 | =COUNTIF($B$2:$I$8,"*"&$L3&"*") | | O3 | =COUNTIF($B$2:$I$8,"*"&$N3&"*") | | K4 | =COUNTIF($B$2:$I$8,"*"&$L4&"*") | | O4 | =COUNTIF($B$2:$I$8,"*"&$N4&"*") | | K5 | =COUNTIF($B$2:$I$8,"*"&$L5&"*") | | O5 | =COUNTIF($B$2:$I$8,"*"&$N5&"*") | | K6 | =COUNTIF($B$2:$I$8,"*"&$L6&"*") | | O6 | =COUNTIF($B$2:$I$8,"*"&$N6&"*") | | O7 | =COUNTIF($B$2:$I$8,"*"&$N7&"*") |
<tbody>
</tbody> |
<tbody>
</tbody>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Then in column A I put the Days (1, 2, 3, etc), or Sunday to Saturday for the entire week or month.
Row 1 is the header row where I put the positions for the cooks and others…see the screenshot below.
Sheet1
| | A | B | C | D | E | F | G | H | I | J |
| 1 | Day | AM Cook | PM Cook | AM1 | AM2 | AM3 | PM4 | PM5 | PM6 | |
| 2 | 1 | Bob | George | Terry | Lenny | Mark | Mike | Sebough | Marty | |
| 3 | 2 | | | | | | | | | Missing |
| 4 | 3 | Les | Steve | | Lenny | Terry | Mark | Sebough | | Missing |
| 5 | 4 | | | | | | | | | Missing |
| 6 | 5 | Sam | Bob | Terry | Lenny | Mark | Mike | Marty | Sebough | |
| 7 | 6 | | | | | | | | | Missing |
| 8 | 7 | | | | | | | | | Missing |
<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>
| Spreadsheet Formulas |
| Cell | Formula | | J2 | =IF(COUNTA(B2:I2)<>8,"Missing","") | | A3 | =A2+1 | | J3 | =IF(COUNTA(B3:I3)<>8,"Missing","") | | A4 | =A3+1 | | J4 | =IF(COUNTA(B4:I4)<>8,"Missing","") | | A5 | =A4+1 | | J5 | =IF(COUNTA(B5:I5)<>8,"Missing","") | | A6 | =A5+1 | | J6 | =IF(COUNTA(B6:I6)<>8,"Missing","") | | A7 | =A6+1 | | J7 | =IF(COUNTA(B7:I7)<>8,"Missing","") | | A8 | =A7+1 | | J8 | =IF(COUNTA(B8:I8)<>8,"Missing","") |
<tbody>
</tbody> |
<tbody>
</tbody>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Then I created 2 two data validation (dropdown) lists, one for Cooks and the 2nd for the Other shifts.
So when you select a cell in the range B2:I7 a dropdown arrow is displayed, press the arrow and a list of
names is shown. If you select columns B or C you will get just the Cooks names. When you select columns
D to I you will get just the Other names. I found it handy to keep track of how many times each name is assigned
so I have a formula that does that by each name as show in the first screenshot above (columns K and O).
If this looks like something you can use, let me know and I will provide the formulas and directions how to create
the sheet. I can also provide a copy of the file using Box.com if you prefer so all you have to do is change the names in columns L and N.
Let me know if this something you would like to look at.
Perpa
