ginstermanuk
New Member
- Joined
- Sep 5, 2012
- Messages
- 17
In the spreadsheet attached, Rows 5 to 11 give a list of job roles and the level of skill those roles should have for a range of subjects, rows 20 to 36 have a list of staff and their job roles with drop downs in the cells underneath the subjects to select their skill levels.
What i'd like to do is once everybodys skill levels have been selected, for the cell to be coloured according to the following rules
If their skill level is higher than the expected level for their job role - Purple
If their skill level is equal to the expected level for their job role - Green
If their skill level is lower than the expected level for their job role - Amber
If their skill level equals "Not started" - Red
If their skill level equals "Not applicable" - Grey
And then at the end of each persons skills (Columns P to T) a total for each colour
Can this be done?
Any help greatly appreciated
What i'd like to do is once everybodys skill levels have been selected, for the cell to be coloured according to the following rules
If their skill level is higher than the expected level for their job role - Purple
If their skill level is equal to the expected level for their job role - Green
If their skill level is lower than the expected level for their job role - Amber
If their skill level equals "Not started" - Red
If their skill level equals "Not applicable" - Grey
And then at the end of each persons skills (Columns P to T) a total for each colour
Can this be done?
Any help greatly appreciated
| 2022 January PMO Skills Matrix and New Starter Dev Plan v0.2_mra.xlsx | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
| 1 | PMO Skills Matrix | |||||||||||||||||||||
| 2 | ||||||||||||||||||||||
| 3 | PROJECT MANAGEMENT QUALIFICATIONS (inc Exams) | |||||||||||||||||||||
| 4 | Generic Role | Band and Role | PRINCE2 Foundation | PRINCE2 Practitioner | APMG Change Management Foundation | APMG Change Management Practitioner | APMG Managing Benefits Foundation | APMG Managing Benefits Practitioner | APMG Better Business Cases Foundation | APMG Better Business Cases Practitioner | Managing Successful Programmes Foundation | Managing Successful Programmes Practitioner | ||||||||||
| 5 | B4 | Admin | B4 Admin | 1 - Awareness | Not applicable | Not applicable | Not applicable | Not applicable | Not applicable | Not applicable | Not applicable | Not applicable | Not applicable | |||||||||
| 6 | B5 | Admin | B5 Admin | 1 - Awareness | Not applicable | 1 - Awareness | Not applicable | 1 - Awareness | Not applicable | Not applicable | Not applicable | Not applicable | Not applicable | |||||||||
| 7 | B5 | Project Officer | B5 Project Officer | 3 - Competent | Not applicable | 3 - Competent | Not applicable | 3 - Competent | Not applicable | Not applicable | Not applicable | Not applicable | Not applicable | |||||||||
| 8 | B6 | Senior Project Officer | B6 Senior Project Officer | 3 - Competent | 3 - Competent | 3 - Competent | Not applicable | 3 - Competent | Not applicable | Not applicable | Not applicable | Not applicable | Not applicable | |||||||||
| 9 | B7 | Project Manager | B7 Project Manager | 3 - Competent | 3 - Competent | 3 - Competent | Not applicable | 3 - Competent | Not applicable | Not applicable | Not applicable | Not applicable | Not applicable | |||||||||
| 10 | B8 | Programme Manager | B8 Programme Manager | 3 - Competent | 3 - Competent | 3 - Competent | 3 - Competent | 3 - Competent | 3 - Competent | 3 - Competent | 3 - Competent | 3 - Competent | 3 - Competent | |||||||||
| 11 | SME | Subject Matter Expert | SME Subject Matter Expert | Not applicable | Not applicable | Not applicable | 4 - Proficient | Not applicable | 4 - Proficient | Not applicable | 4 - Proficient | Not applicable | Not applicable | |||||||||
| 12 | ||||||||||||||||||||||
| 18 | ||||||||||||||||||||||
| 19 | Team Specific | Totals | ||||||||||||||||||||
| 20 | First Name | Surname | Band | Role | Band and Role | |||||||||||||||||
| 21 | B6 | Senior Project Officer | B6 Senior Project Officer | |||||||||||||||||||
| 22 | B5 | Project Officer | B5 Project Officer | |||||||||||||||||||
| 23 | B5 | Project Officer | B5 Project Officer | |||||||||||||||||||
| 24 | B8 | Programme Manager | B8 Programme Manager | 3 - Competent | ||||||||||||||||||
| 25 | B6 | Senior Project Officer | B6 Senior Project Officer | |||||||||||||||||||
| 26 | B5 | Project Officer | B5 Project Officer | |||||||||||||||||||
| 27 | B5 | Admin | B5 Admin | |||||||||||||||||||
| 28 | B6 | Senior Project Officer | B6 Senior Project Officer | |||||||||||||||||||
| 29 | B8 | Programme Manager | B8 Programme Manager | |||||||||||||||||||
| 30 | B5 | Project Officer | B5 Project Officer | |||||||||||||||||||
| 31 | B8 | Programme Manager | B8 Programme Manager | |||||||||||||||||||
| 32 | B6 | Senior Project Officer | B6 Senior Project Officer | |||||||||||||||||||
| 33 | B7 | Project Manager | B7 Project Manager | |||||||||||||||||||
| 34 | B7 | Project Manager | B7 Project Manager | |||||||||||||||||||
| 35 | B4 | Admin | B4 Admin | |||||||||||||||||||
| 36 | B8 | Programme Manager | B8 Programme Manager | |||||||||||||||||||
| 37 | B6 | Senior Project Officer | B6 Senior Project Officer | On secondment to a non DPT role | ||||||||||||||||||
| 38 | B7 | Project Manager | B7 Project Manager | On secondment to a non DPT role | ||||||||||||||||||
Skills Matrix (3) | ||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E5:E11 | E5 | =CONCATENATE(A5," ",B5) |
| E21:E38 | E21 | =CONCATENATE(C21," ",D21) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| J6 | Cell Value | ="5 - Expert" | text | NO |
| J6 | Cell Value | ="4 - Proficient" | text | NO |
| J6 | Cell Value | ="3 - Competent" | text | NO |
| J6 | Cell Value | ="2 - Practised" | text | NO |
| J6 | Cell Value | ="1 - Awareness" | text | NO |
| J6 | Cell Value | ="Not applicable" | text | NO |
| J5 | Cell Value | ="5 - Expert" | text | NO |
| J5 | Cell Value | ="4 - Proficient" | text | NO |
| J5 | Cell Value | ="3 - Competent" | text | NO |
| J5 | Cell Value | ="2 - Practised" | text | NO |
| J5 | Cell Value | ="1 - Awareness" | text | NO |
| J5 | Cell Value | ="Not applicable" | text | NO |
| H6 | Cell Value | ="5 - Expert" | text | NO |
| H6 | Cell Value | ="4 - Proficient" | text | NO |
| H6 | Cell Value | ="3 - Competent" | text | NO |
| H6 | Cell Value | ="2 - Practised" | text | NO |
| H6 | Cell Value | ="1 - Awareness" | text | NO |
| H6 | Cell Value | ="Not applicable" | text | NO |
| H5 | Cell Value | ="5 - Expert" | text | NO |
| H5 | Cell Value | ="4 - Proficient" | text | NO |
| H5 | Cell Value | ="3 - Competent" | text | NO |
| H5 | Cell Value | ="2 - Practised" | text | NO |
| H5 | Cell Value | ="1 - Awareness" | text | NO |
| H5 | Cell Value | ="Not applicable" | text | NO |
| F6:G6,I6,K6:O6 | Cell Value | ="5 - Expert" | text | NO |
| F6:G6,I6,K6:O6 | Cell Value | ="4 - Proficient" | text | NO |
| F6:G6,I6,K6:O6 | Cell Value | ="3 - Competent" | text | NO |
| F6:G6,I6,K6:O6 | Cell Value | ="2 - Practised" | text | NO |
| F6:G6,I6,K6:O6 | Cell Value | ="1 - Awareness" | text | NO |
| F6:G6,I6,K6:O6 | Cell Value | ="Not applicable" | text | NO |
| F5:G5,I5,K5:O5 | Cell Value | ="5 - Expert" | text | NO |
| F5:G5,I5,K5:O5 | Cell Value | ="4 - Proficient" | text | NO |
| F5:G5,I5,K5:O5 | Cell Value | ="3 - Competent" | text | NO |
| F5:G5,I5,K5:O5 | Cell Value | ="2 - Practised" | text | NO |
| F5:G5,I5,K5:O5 | Cell Value | ="1 - Awareness" | text | NO |
| F5:G5,I5,K5:O5 | Cell Value | ="Not applicable" | text | NO |
| F7:O11 | Cell Value | ="5 - Expert" | text | NO |
| F7:O11 | Cell Value | ="4 - Proficient" | text | NO |
| F7:O11 | Cell Value | ="3 - Competent" | text | NO |
| F7:O11 | Cell Value | ="2 - Practised" | text | NO |
| F7:O11 | Cell Value | ="1 - Awareness" | text | NO |
| F7:O11 | Cell Value | ="Not applicable" | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| F5:O11 | List | ='DRop Downs'!$A$2:$A$8 |
| F21:O36 | List | ='DRop Downs'!$A$2:$A$8 |
| F37:O38 | List | Not applicable, 0 - Not started, 1 - Awareness, 2 - Practised, 3 - Competent, 4 - Proficient, 5 - Expert |
