Can this be done in a conditional format formula?

[ginstermanuk]

Hello all,
Above are two snapshots from an Excel spreadsheet i am developing, for each role and qualification is a drop down (0-5 and Not applicable), these are considered what the role should have so are fixed values.
Next to the name and banding is where the values associated for the individual are selected, what id like to do is colour code these automatically according to their banding and what they have selected. i.e if their band is B6 and for Prince 2 they should be a 3-Competent and they select 3 then that cell becomes green, if their selection is lower than what it should be then the cell should be amber and if it is higher then the cell should be purple.

So my question is can this be done via conditional formatting and any help will be greatly appreciated.

Thanks

 

[shknbk2]

Here's a first shot at what I could come up with. It seems to work fine as long as the Band is not B5 and the person's role is Project Officer. This code below is based only a single-cell Index/Match: the band in column C is compared with the bands in A5:A11. Since there are two B5s, this example will only find the first one. There are online examples of searching for multiple criteria (like matching the band and the title), but I'm not sure where your title information would be: it's not in your second example. Dave Smith is B5, but which B5 is he? For the unique band values, I think this is pretty close.

Book1
	A	B	C	D
5	B4	Admin		1 - Awareness
6	B5	Admin		1 - Awareness
7	B5	Project Officer		3 - Competent
8	B6	Senior Project Officer		3 - Competent
9	B7	Project Manager		Not applicable
10
11
12
13
14
15
16			Band
17	Joe	bloggs	B6	3 - Competent
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D17	Expression	=NUMBERVALUE(LEFT(INDEX($D$5:$D$9,MATCH($C17,$A$5:$A$9,0)),1))<NUMBERVALUE(LEFT(D17,1))	text	YES
D17	Expression	=NUMBERVALUE(LEFT(INDEX($D$5:$D$9,MATCH($C17,$A$5:$A$9,0)),1))=NUMBERVALUE(LEFT(D17,1))	text	YES
D17	Expression	=NUMBERVALUE(LEFT(INDEX($D$5:$D$9,MATCH($C17,$A$5:$A$9,0)),1))>NUMBERVALUE(LEFT(D17,1))	text	YES

Cells with Data Validation
Cell	Allow	Criteria
D17	List	=$P$1:$P$6

 

[ginstermanuk]

I have rejigged the spreadsheet in the hope that this can be done in an easier way

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

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

 

[shknbk2]

I've used a helper column (G, I, K, etc.) to calculate the difference or other value (6, 7, 8) between the person's level and the expected level. The helper columns aren't needed for the conditional formatting, but they are needed for the totals because you can't count conditional formatting colors. I have left the helper columns visible below, but you can hide them to make the sheet cleaner. I also didn't care about including them in the conditional formatting in order to avoid having a billion different formatting rules. Since they are hidden, they're colors aren't visible, and the lump range makes formatting easier.

Skill level.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
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			5 - Expert
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			4 - Proficient
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			3 - Competent
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			2 - Practised
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			1 - Awareness
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			0 - Not started
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			Not applicable
12
13
14
15
16
17
18
19	Team Specific																									Totals
20	First Name	Surname	Band	Role	Band and Role
21			B6	Senior Project Officer	B6 Senior Project Officer	0 - Not started	7		8		8		8		8		8		8		8		8		8	0	1	0	0	0
22			B5	Project Officer	B5 Project Officer	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
23			B5	Project Officer	B5 Project Officer	Not applicable	6		8		8		8		8		8		8		8		8		8	0	0	0	0	1
24			B8	Programme Manager	B8 Programme Manager	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
25			B6	Senior Project Officer	B6 Senior Project Officer	5 - Expert	-2		8		8		8		8		8		8		8		8		8	1	0	0	0	0
26			B5	Project Officer	B5 Project Officer	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
27			B5	Admin	B5 Admin	3 - Competent	-2		8		8		8		8		8		8		8		8		8	1	0	0	0	0
28			B6	Senior Project Officer	B6 Senior Project Officer	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
29			B8	Programme Manager	B8 Programme Manager	2 - Practised	1		8		8		8		8		8		8		8		8		8	0	0	1	0	0
30			B5	Project Officer	B5 Project Officer	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
31			B8	Programme Manager	B8 Programme Manager	Not applicable	6		8		8		8		8		8		8		8		8		8	0	0	0	0	1
32			B6	Senior Project Officer	B6 Senior Project Officer	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
33			B7	Project Manager	B7 Project Manager	1 - Awareness	2		8		8		8		8		8		8		8		8		8	0	0	1	0	0
34			B7	Project Manager	B7 Project Manager	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
35			B4	Admin	B4 Admin	3 - Competent	-2		8		8		8		8		8		8		8		8		8	1	0	0	0	0
36			B8	Programme Manager	B8 Programme Manager	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
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
Sheet1

Cell Formulas
Range	Formula
E5:E11	E5	=CONCATENATE(A5," ",B5)
Y21:Y36,W21:W36,U21:U36,S21:S36,Q21:Q36,O21:O36,M21:M36,K21:K36,I21:I36,G21:G36	Y21	=IF(X21=0,8,IF(X21="0 - Not started",7, IFERROR(NUMBERVALUE(LEFT(INDEX(X$5:X$11,MATCH($E21,$E$5:$E$11,0)),1))-NUMBERVALUE(LEFT(X21,1)),6)))
Z21:Z36	Z21	=COUNTIF($F21:$Y21,"<0")
AA21:AA36	AA21	=COUNTIF($F21:$Y21,"=7")
AB21:AB36	AB21	=COUNTIFS($F21:$Y21,">0",$F21:$Y21,"<6")
AC21:AC36	AC21	=COUNTIF($F21:$Y21,"=0")
AD21:AD36	AD21	=COUNTIF($F21:$Y21,"=6")
E21:E38	E21	=CONCATENATE(C21," ",D21)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F21:Y36	Expression	=G21=8	text	YES
F21:Y36	Expression	=G21=7	text	YES
F21:Y36	Expression	=G21=6	text	YES
F21:Y36	Expression	=G21>0	text	NO
F21:Y36	Expression	=G21=0	text	NO
F21:Y36	Expression	=G21<0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F21:F36	List	=$AA$5:$AA$11
F5:Y11	List	=$AA$5:$AA$11
J21:J36	List	=$AA$5:$AA$11
P21:P36	List	=$AA$5:$AA$11
T21:T36	List	=$AA$5:$AA$11
X21:X36	List	=$AA$5:$AA$11
H21:H36	List	=$AA$5:$AA$11
V21:V36	List	=$AA$5:$AA$11
R21:R36	List	=$AA$5:$AA$11
N21:N36	List	=$AA$5:$AA$11
L21:L36	List	=$AA$5:$AA$11

 

[ginstermanuk]

I've used a helper column (G, I, K, etc.) to calculate the difference or other value (6, 7, 8) between the person's level and the expected level. The helper columns aren't needed for the conditional formatting, but they are needed for the totals because you can't count conditional formatting colors. I have left the helper columns visible below, but you can hide them to make the sheet cleaner. I also didn't care about including them in the conditional formatting in order to avoid having a billion different formatting rules. Since they are hidden, they're colors aren't visible, and the lump range makes formatting easier.

Skill level.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
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			5 - Expert
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			4 - Proficient
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			3 - Competent
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			2 - Practised
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			1 - Awareness
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			0 - Not started
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			Not applicable
12
13
14
15
16
17
18
19	Team Specific																									Totals
20	First Name	Surname	Band	Role	Band and Role
21			B6	Senior Project Officer	B6 Senior Project Officer	0 - Not started	7		8		8		8		8		8		8		8		8		8	0	1	0	0	0
22			B5	Project Officer	B5 Project Officer	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
23			B5	Project Officer	B5 Project Officer	Not applicable	6		8		8		8		8		8		8		8		8		8	0	0	0	0	1
24			B8	Programme Manager	B8 Programme Manager	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
25			B6	Senior Project Officer	B6 Senior Project Officer	5 - Expert	-2		8		8		8		8		8		8		8		8		8	1	0	0	0	0
26			B5	Project Officer	B5 Project Officer	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
27			B5	Admin	B5 Admin	3 - Competent	-2		8		8		8		8		8		8		8		8		8	1	0	0	0	0
28			B6	Senior Project Officer	B6 Senior Project Officer	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
29			B8	Programme Manager	B8 Programme Manager	2 - Practised	1		8		8		8		8		8		8		8		8		8	0	0	1	0	0
30			B5	Project Officer	B5 Project Officer	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
31			B8	Programme Manager	B8 Programme Manager	Not applicable	6		8		8		8		8		8		8		8		8		8	0	0	0	0	1
32			B6	Senior Project Officer	B6 Senior Project Officer	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
33			B7	Project Manager	B7 Project Manager	1 - Awareness	2		8		8		8		8		8		8		8		8		8	0	0	1	0	0
34			B7	Project Manager	B7 Project Manager	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
35			B4	Admin	B4 Admin	3 - Competent	-2		8		8		8		8		8		8		8		8		8	1	0	0	0	0
36			B8	Programme Manager	B8 Programme Manager	3 - Competent	0		8		8		8		8		8		8		8		8		8	0	0	0	1	0
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
Sheet1

Cell Formulas
Range	Formula
E5:E11	E5	=CONCATENATE(A5," ",B5)
Y21:Y36,W21:W36,U21:U36,S21:S36,Q21:Q36,O21:O36,M21:M36,K21:K36,I21:I36,G21:G36	Y21	=IF(X21=0,8,IF(X21="0 - Not started",7, IFERROR(NUMBERVALUE(LEFT(INDEX(X$5:X$11,MATCH($E21,$E$5:$E$11,0)),1))-NUMBERVALUE(LEFT(X21,1)),6)))
Z21:Z36	Z21	=COUNTIF($F21:$Y21,"<0")
AA21:AA36	AA21	=COUNTIF($F21:$Y21,"=7")
AB21:AB36	AB21	=COUNTIFS($F21:$Y21,">0",$F21:$Y21,"<6")
AC21:AC36	AC21	=COUNTIF($F21:$Y21,"=0")
AD21:AD36	AD21	=COUNTIF($F21:$Y21,"=6")
E21:E38	E21	=CONCATENATE(C21," ",D21)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F21:Y36	Expression	=G21=8	text	YES
F21:Y36	Expression	=G21=7	text	YES
F21:Y36	Expression	=G21=6	text	YES
F21:Y36	Expression	=G21>0	text	NO
F21:Y36	Expression	=G21=0	text	NO
F21:Y36	Expression	=G21<0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F21:F36	List	=$AA$5:$AA$11
F5:Y11	List	=$AA$5:$AA$11
J21:J36	List	=$AA$5:$AA$11
P21:P36	List	=$AA$5:$AA$11
T21:T36	List	=$AA$5:$AA$11
X21:X36	List	=$AA$5:$AA$11
H21:H36	List	=$AA$5:$AA$11
V21:V36	List	=$AA$5:$AA$11
R21:R36	List	=$AA$5:$AA$11
N21:N36	List	=$AA$5:$AA$11
L21:L36	List	=$AA$5:$AA$11

Many thanks for this, just tried transposing over to my spreadsheet and it looks like ive done something wrong with the conditional formatting, any ideas where ive gone wrong? The colours seem to be ok apart from the Not Started which is grey not red

Thanks

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
21			B6	Senior Project Officer	B6 Senior Project Officer	Not applicable	6	Not Started	6	1 - Awareness	2	Not applicable
22			B5	Project Officer	B5 Project Officer	5 - Expert	-2	1 - Awareness	6	2 - Practised	1	3 - Competent
23			B5	Project Officer	B5 Project Officer	1 - Awareness	2	2 - Practised	6	3 - Competent	0	4 - Proficient
24			B8	Programme Manager	B8 Programme Manager	2 - Practised	1	3 - Competent	0	4 - Proficient	-1	5 - Expert
25			B6	Senior Project Officer	B6 Senior Project Officer	3 - Competent	0	4 - Proficient	-1	5 - Expert	-2	Not applicable
26			B5	Project Officer	B5 Project Officer	4 - Proficient	-1	5 - Expert	6	Not applicable	6	Not Started
27			B5	Admin	B5 Admin	5 - Expert	-4	Not applicable	6	Not Started	6	1 - Awareness
28			B6	Senior Project Officer	B6 Senior Project Officer	Not applicable	6	Not Started	6	1 - Awareness	2	2 - Practised
29			B8	Programme Manager	B8 Programme Manager	Not Started	6	1 - Awareness	2	2 - Practised	1	3 - Competent
30			B5	Project Officer	B5 Project Officer	1 - Awareness	2	2 - Practised	6	3 - Competent	0	4 - Proficient
31			B8	Programme Manager	B8 Programme Manager	2 - Practised	1	3 - Competent	0	4 - Proficient	-1	5 - Expert
32			B6	Senior Project Officer	B6 Senior Project Officer	3 - Competent	0	4 - Proficient	-1	5 - Expert	-2	Not applicable
33			B7	Project Manager	B7 Project Manager	4 - Proficient	-1	5 - Expert	-2	Not applicable	6	Not Started
34			B7	Project Manager	B7 Project Manager	5 - Expert	-2	Not applicable	6	Not Started	6
35			B4	Admin	B4 Admin	Not applicable	6	Not Started	6		8
36			B8	Programme Manager	B8 Programme Manager	Not Started	6		8		8
Skills Matrix (2)

Cell Formulas
Range	Formula
E21:E36	E21	=CONCATENATE(C21," ",D21)
K21:K36,I21:I36,G21:G36	G21	=IF(F21=0,8,IF(F21="0 - Not started",7, IFERROR(NUMBERVALUE(LEFT(INDEX(F$5:F$11,MATCH($E21,$E$5:$E$11,0)),1))-NUMBERVALUE(LEFT(F21,1)),6)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F21:FI36	Expression	=G21=8	text	YES
F21:FI36	Expression	=G21=7	text	YES
F21:FI36	Expression	=G21=6	text	YES
F21:FI36	Expression	=G21>0	text	NO
F21:FI36	Expression	=G21=0	text	NO
F21:FI36	Expression	=G21<0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F21:F36	List	=$FO$19:$FO$25
H21:H36	List	=$FO$19:$FO$25
J21:J36	List	=$FO$19:$FO$25
L21:L36	List	=$FO$19:$FO$25

 

[shknbk2]

The purple condition is "=WZH21<0". Change it to G21<0.

 

[ginstermanuk]

The purple condition is "=WZH21<0". Change it to G21<0.

Sorry i fixed that when i posted it, its the "not started" which is coming out as grey instead of red which is the problem now as in the edited post no. 5

 

[shknbk2]

In post #4, you had "0 - Not Started". In #5, you just have "Not Started". Change the If formula to remove "0 - ".

Edit: Or probably better, yet, change it to "IF(F21=$FO$19" or whichever of these cells has Not Started.