Returning column names based on status

[isometric]

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.

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

 

[Herakles]

If your data was restructured you could use the Data - Filter functionality that is in 2019.

StaffCourses sheet:
So columns as follows:
Staff
Course
Deadline
Status

The Deadine column is a lookup on the Courses sheet.

Courses sheet:
Course
Deadline

Staff sheet:
Staff
InProgress
Dropped
Finished

Columns InProgress, Dropped and Finished have formula which use a COUNTIF on the StaffCourses sheet.

Having a structure such as this will make analysis and data management easier.

All you have to do is add rows to sheets instead of columns.

The formula's will not need to be amended as you add courses.

 

[Sufiyan97]

Try

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
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	5/18/2023	5/15/2023	5/15/2023	6/10/2023	6/10/2023
8
9	STAFF													IN PROGRESS
10	STAFF A	IN PROGRESS	IN PROGRESS	IN PROGRESS	FINISHED	FINISHED		3	0	2			STAFF C	TRAINING 1
11	STAFF B	DROPPED	IN PROGRESS	FINISHED	FINISHED	FINISHED		1	1	3				TRAINING 5
12	STAFF C	IN PROGRESS	FINISHED	DROPPED	FINISHED	IN PROGRESS		2	1	2
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
15
16
17
18
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")
N10:N13	N10	=IFERROR(INDEX($B$6:$F$6,,AGGREGATE(15,6,(COLUMN($B$6:$F$6)-COLUMN($B$6)+1)/($A$10:$A$14=$M$10)/ISNUMBER(SEARCH($N$9,$B$10:$F$14)),ROWS($N$10:N10))),"")

 

[isometric]

Try

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
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	5/18/2023	5/15/2023	5/15/2023	6/10/2023	6/10/2023
8
9	STAFF													IN PROGRESS
10	STAFF A	IN PROGRESS	IN PROGRESS	IN PROGRESS	FINISHED	FINISHED		3	0	2			STAFF C	TRAINING 1
11	STAFF B	DROPPED	IN PROGRESS	FINISHED	FINISHED	FINISHED		1	1	3				TRAINING 5
12	STAFF C	IN PROGRESS	FINISHED	DROPPED	FINISHED	IN PROGRESS		2	1	2
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
15
16
17
18
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")
N10:N13	N10	=IFERROR(INDEX($B$6:$F$6,,AGGREGATE(15,6,(COLUMN($B$6:$F$6)-COLUMN($B$6)+1)/($A$10:$A$14=$M$10)/ISNUMBER(SEARCH($N$9,$B$10:$F$14)),ROWS($N$10:N10))),"")

Works like a charm thank you! IFERROR strings always confuse me.

 

[Sufiyan97]

You're welcome
Glad I could help.

Have a look at

IFERROR Function

The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.

exceljet.net