Good day all
I'm making a training matrix and want to add a few QOL.
I want to list a staff's courses that are still in progress like in N10. I know it would be easier with the FILTER function but unfortunately we have office 2019.
I'm making a training matrix and want to add a few QOL.
I want to list a staff's courses that are still in progress like in N10. I know it would be easier with the FILTER function but unfortunately we have office 2019.
TRAINING MATRIX DRAFT.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | TRAINING MATRIX | |||||||||||||||
2 | ||||||||||||||||
3 | IN PROGRESS | |||||||||||||||
4 | DROPPED | |||||||||||||||
5 | FINISHED | |||||||||||||||
6 | TRAINING 1 | TRAINING 2 | TRAINING 3 | TRAINING 4 | TRAINING 5 | IN PROGRESS | DROPPED | FINISHED | ||||||||
7 | DEADLINE | 18/05/2023 | 15/05/2023 | 15/05/2023 | 10/06/2023 | 10/06/2023 | ||||||||||
8 | ||||||||||||||||
9 | STAFF | COURSES IN PROGRESS | ||||||||||||||
10 | STAFF A | IN PROGRESS | IN PROGRESS | IN PROGRESS | FINISHED | FINISHED | 3 | 0 | 2 | STAFF A | TRAINING 1 | |||||
11 | STAFF B | DROPPED | IN PROGRESS | FINISHED | FINISHED | FINISHED | 1 | 1 | 3 | TRAINING 2 | ||||||
12 | STAFF C | IN PROGRESS | FINISHED | DROPPED | FINISHED | IN PROGRESS | 2 | 1 | 2 | TRAINING 3 | ||||||
13 | STAFF D | DROPPED | FINISHED | FINISHED | IN PROGRESS | FINISHED | 1 | 1 | 3 | |||||||
14 | STAFF E | FINISHED | FINISHED | IN PROGRESS | IN PROGRESS | DROPPED | 2 | 1 | 2 | |||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H10:H14 | H10 | =COUNTIF(B10:F10,"IN PROGRESS") |
I10:I14 | I10 | =COUNTIF(B10:F10,"DROPPED") |
J10:J14 | J10 | =COUNTIF(B10:F10,"FINISHED") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
B10:F14 | Cell Value | =$A$5 | text | NO |
B10:F14 | Cell Value | =$A$4 | text | NO |
B10:F14 | Cell Value | =$A$3 | text | NO |
B14:F14 | Cell Value | =3 | text | NO |
B14:F14 | Cell Value | =2 | text | NO |
B14:F14 | Cell Value | =1 | text | NO |
B13:F13 | Cell Value | =3 | text | NO |
B13:F13 | Cell Value | =2 | text | NO |
B13:F13 | Cell Value | =1 | text | NO |
B12:F12 | Cell Value | =3 | text | NO |
B12:F12 | Cell Value | =2 | text | NO |
B12:F12 | Cell Value | =1 | text | NO |
B11:F11 | Cell Value | =3 | text | NO |
B11:F11 | Cell Value | =2 | text | NO |
B11:F11 | Cell Value | =1 | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
B10:F14 | List | =$A$3:$A$5 |