I'm trying to make a counter so I can track how many times my staff have performed a certain job on the rotas I create. I'm new into the position and they're used to the current form; I'm hoping not to change it. The counter is on the right side of the rota, seen below. I only have access to Excel 2016 at home, but Office 365 at work, so there is flexibility but responses to 365 solutions will be slow as I will have to email the spreadsheet back across.
Ideally, any solution would be able to account for the rotas to repeat downwards. In the example below I have weeks 49-51, but I would like to be able to have record of the whole year on a single, albeit long, sheet.
All feedback is appreciated.
Ideally, any solution would be able to account for the rotas to repeat downwards. In the example below I have weeks 49-51, but I would like to be able to have record of the whole year on a single, albeit long, sheet.
All feedback is appreciated.
Book1.xlsx | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | T | U | V | W | X | Y | Z | |||||||||
5 | Week 49 | 05-Dec | 06-Dec | 07-Dec | 08-Dec | 09-Dec | |||||||||||||||||||||
6 | Reception 08:30 - 16:30 | Terry | (1) | Terry | (2) | Hermione | (3) | Terry | (1) | Terry | (2) | ||||||||||||||||
7 | Reception 09:00 - 17:00 | Hermione | (2) | Hermione | (3) | Terry | (1) | Hermione | (2) | Hermione | (3) | ||||||||||||||||
8 | Reception 11.00 - 19:00 | Dillon (12-7) | (3) | Kerry | (1) | Dillon (2-7) | (2) | Kerry | (3) | Kerry | (1) | ||||||||||||||||
9 | Phones 09:00 - 17:00 | Steve | Steve | Steve | Steve | Steve | |||||||||||||||||||||
10 | Phones 09:00 - 17:00 | Jean | Jean | Jean | Jean | Jean | |||||||||||||||||||||
11 | Phones 09:00 - 17:00 | Dom | Dom | Dom | Dom | Dom | |||||||||||||||||||||
12 | Corporate 08:30 - 16:30 | Bob | Bob | Bob | Bob | ||||||||||||||||||||||
13 | Corporate 09:00 - 17:00 | Kerry | |||||||||||||||||||||||||
14 | Corporate 09:00 - 17:00 | ||||||||||||||||||||||||||
15 | |||||||||||||||||||||||||||
16 | Week 50 | 12-Dec | 13-Dec | 14-Dec | 15-Dec | 16-Dec | |||||||||||||||||||||
17 | Reception 08:30 - 16:30 | Jean | (1) | Jean | (2) | Jean | (3) | Jean | (1) | Jean | (2) | Name | Reception 08:30 - 16:30 | Reception 09:00 - 17:00 | Reception 11:00 - 19:00 | Phones 09:00 - 17:00 | Corporate 08:30 - 16:30 | Corporate 09:00 - 17:00 | |||||||||
18 | Reception 09:00 - 17:00 | Steve | (2) | Steve | (3) | Steve | (1) | Steve | (2) | Steve | (3) | Terry | |||||||||||||||
19 | Reception 11.00 - 19:00 | Dillon (12-7) | (3) | Dom | (1) | Dillon (2-7) | (2) | Bob | (3) | Bob | (1) | Hermione | |||||||||||||||
20 | Phones 09:00 - 17:00 | Terry | Terry | Terry | Terry | Terry | Bob | ||||||||||||||||||||
21 | Phones 09:00 - 17:00 | Kerry | Kerry | Kerry | Kerry | Kerry | Jean | ||||||||||||||||||||
22 | Phones 09:00 - 17:00 | Hermione | Hermione | Hermione | Hermione | Hermione | Dom | ||||||||||||||||||||
23 | Corporate 08:30 - 16:30 | Dom | Dom | Dom | Dom | Jack | |||||||||||||||||||||
24 | Corporate 09:00 - 17:00 | Bob | Steve | ||||||||||||||||||||||||
25 | Corporate 09:00 - 17:00 | Dillon | |||||||||||||||||||||||||
26 | Kerry | ||||||||||||||||||||||||||
27 | Week 51 | 19-Dec | 20-Dec | 21-Dec | 22-Dec | 23-Dec | |||||||||||||||||||||
28 | Reception 08:30 - 16:30 | Dom | (1) | Dom | (2) | Dom | (3) | Dom | (1) | Dom | (2) | ||||||||||||||||
29 | Reception 09:00 - 17:00 | Jack | (2) | Jack | (3) | Terry | (1) | Jack | (2) | Jack | (3) | ||||||||||||||||
30 | Reception 11.00 - 19:00 | Dillon (12-7) | (3) | Terry | (1) | Jack | (2) | Terry | (3) | Terry | (1) | ||||||||||||||||
31 | Phones 09:00 - 17:00 | Bob | Hermione | Jean | Hermione | Steve | |||||||||||||||||||||
32 | Phones 09:00 - 17:00 | Kerry | Bob | Bob | Bob | Bob | |||||||||||||||||||||
33 | Phones 09:00 - 17:00 | Steve | Steve | Hermione | Jean | Jean | |||||||||||||||||||||
34 | Corporate 08:30 - 16:30 | Jean | Jean | Steve | Steve | Hermione | |||||||||||||||||||||
35 | Corporate 09:00 - 17:00 | Hermione | Kerry | ||||||||||||||||||||||||
36 | Corporate 09:00 - 17:00 | Terry | |||||||||||||||||||||||||
Rota 3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E5,K27,I27,G27,E27,K16,I16,G16,E16,K5,I5,G5 | E5 | =C5+1 |
C16,C27 | C16 | =C5+7 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
C6:Z4999 | Cell Value | contains "Nafisa" | text | NO |
C6:Z4999 | Cell Value | contains "Kirsten" | text | NO |
C6:Z4999 | Cell Value | contains "Matilda" | text | NO |
C6:Z4999 | Cell Value | contains "Lorelei" | text | NO |
C6:Z4999 | Cell Value | contains "Abigail" | text | NO |
C6:Z4999 | Cell Value | contains "Kyle" | text | NO |
C6:Z4999 | Cell Value | contains "Chris" | text | NO |
C6:Z4999 | Cell Value | contains "Georgia N" | text | NO |
C6:Z4999 | Cell Value | contains "Georgia S" | text | NO |
C6:Z4999 | Cell Value | contains "Nafisa" | text | NO |
C6:Z4999 | Cell Value | contains "Kirsten" | text | NO |
C6:Z4999 | Cell Value | contains "Matilda" | text | NO |
C6:Z4999 | Cell Value | contains "Lorelei" | text | NO |
C6:Z4999 | Cell Value | contains "Abigail" | text | NO |
C6:Z4999 | Cell Value | contains "Kyle" | text | NO |
C6:Z4999 | Cell Value | contains "Chris" | text | NO |
C6:Z4999 | Cell Value | contains "Georgia N" | text | NO |
C6:Z4999 | Cell Value | contains "Georgia S" | text | NO |
L28:L30 | Cell | contains a blank value | text | NO |
L28:L30 | Cell Value | contains "Nafisa" | text | NO |
L28:L30 | Cell Value | contains "Kirsten" | text | NO |
L28:L30 | Cell Value | contains "Matilda" | text | NO |
L28:L30 | Cell Value | contains "Lorelei" | text | NO |
L28:L30 | Cell Value | contains "Abigail" | text | NO |
L28:L30 | Cell Value | contains "Kyle" | text | NO |
L28:L30 | Cell Value | contains "Chris" | text | NO |
L28:L30 | Cell Value | contains "Georgia N" | text | NO |
L28:L30 | Cell Value | contains "Georgia S" | text | NO |
J28:J30 | Cell | contains a blank value | text | NO |
J28:J30 | Cell Value | contains "Nafisa" | text | NO |
J28:J30 | Cell Value | contains "Kirsten" | text | NO |
J28:J30 | Cell Value | contains "Matilda" | text | NO |
J28:J30 | Cell Value | contains "Lorelei" | text | NO |
J28:J30 | Cell Value | contains "Abigail" | text | NO |
J28:J30 | Cell Value | contains "Kyle" | text | NO |
J28:J30 | Cell Value | contains "Chris" | text | NO |
J28:J30 | Cell Value | contains "Georgia N" | text | NO |
J28:J30 | Cell Value | contains "Georgia S" | text | NO |
H28:H30 | Cell | contains a blank value | text | NO |
H28:H30 | Cell Value | contains "Nafisa" | text | NO |
H28:H30 | Cell Value | contains "Kirsten" | text | NO |
H28:H30 | Cell Value | contains "Matilda" | text | NO |
H28:H30 | Cell Value | contains "Lorelei" | text | NO |
H28:H30 | Cell Value | contains "Abigail" | text | NO |
H28:H30 | Cell Value | contains "Kyle" | text | NO |
H28:H30 | Cell Value | contains "Chris" | text | NO |
H28:H30 | Cell Value | contains "Georgia N" | text | NO |
H28:H30 | Cell Value | contains "Georgia S" | text | NO |
F28:F30 | Cell | contains a blank value | text | NO |
F28:F30 | Cell Value | contains "Nafisa" | text | NO |
F28:F30 | Cell Value | contains "Kirsten" | text | NO |
F28:F30 | Cell Value | contains "Matilda" | text | NO |
F28:F30 | Cell Value | contains "Lorelei" | text | NO |
F28:F30 | Cell Value | contains "Abigail" | text | NO |
F28:F30 | Cell Value | contains "Kyle" | text | NO |
F28:F30 | Cell Value | contains "Chris" | text | NO |
F28:F30 | Cell Value | contains "Georgia N" | text | NO |
F28:F30 | Cell Value | contains "Georgia S" | text | NO |
D28:D30 | Cell | contains a blank value | text | NO |
D28:D30 | Cell Value | contains "Nafisa" | text | NO |
D28:D30 | Cell Value | contains "Kirsten" | text | NO |
D28:D30 | Cell Value | contains "Matilda" | text | NO |
D28:D30 | Cell Value | contains "Lorelei" | text | NO |
D28:D30 | Cell Value | contains "Abigail" | text | NO |
D28:D30 | Cell Value | contains "Kyle" | text | NO |
D28:D30 | Cell Value | contains "Chris" | text | NO |
D28:D30 | Cell Value | contains "Georgia N" | text | NO |
D28:D30 | Cell Value | contains "Georgia S" | text | NO |
C17:L25,C6:L14 | Cell | contains a blank value | text | NO |
C28:Z4999 | Cell Value | contains "Nafisa" | text | NO |
C6:Z4999 | Cell Value | contains "Kirsten" | text | NO |
C6:Z4999 | Cell Value | contains "Matilda" | text | NO |
C6:Z4999 | Cell Value | contains "Lorelei" | text | NO |
M28:Z36,C6:Z27 | Cell Value | contains "Abigail" | text | NO |
C6:Z4999 | Cell Value | contains "Kyle" | text | NO |
C6:Z4999 | Cell Value | contains "Chris" | text | NO |
C6:Z4999 | Cell Value | contains "Georgia N" | text | NO |
M28:Z36,C6:Z27 | Cell Value | contains "Georgia S" | text | NO |