Returning column names based on status

isometric

New Member
Joined
May 9, 2023
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMN
1TRAINING MATRIX
2
3IN PROGRESS
4DROPPED
5FINISHED
6TRAINING 1TRAINING 2TRAINING 3TRAINING 4TRAINING 5IN PROGRESSDROPPEDFINISHED
7DEADLINE18/05/202315/05/202315/05/202310/06/202310/06/2023
8
9STAFFCOURSES IN PROGRESS
10STAFF AIN PROGRESSIN PROGRESSIN PROGRESSFINISHEDFINISHED302STAFF ATRAINING 1
11STAFF BDROPPEDIN PROGRESSFINISHEDFINISHEDFINISHED113TRAINING 2
12STAFF CIN PROGRESSFINISHEDDROPPEDFINISHEDIN PROGRESS212TRAINING 3
13STAFF DDROPPEDFINISHEDFINISHEDIN PROGRESSFINISHED113
14STAFF EFINISHEDFINISHEDIN PROGRESSIN PROGRESSDROPPED212
Sheet1
Cell Formulas
RangeFormula
H10:H14H10=COUNTIF(B10:F10,"IN PROGRESS")
I10:I14I10=COUNTIF(B10:F10,"DROPPED")
J10:J14J10=COUNTIF(B10:F10,"FINISHED")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B10:F14Cell Value=$A$5textNO
B10:F14Cell Value=$A$4textNO
B10:F14Cell Value=$A$3textNO
B14:F14Cell Value=3textNO
B14:F14Cell Value=2textNO
B14:F14Cell Value=1textNO
B13:F13Cell Value=3textNO
B13:F13Cell Value=2textNO
B13:F13Cell Value=1textNO
B12:F12Cell Value=3textNO
B12:F12Cell Value=2textNO
B12:F12Cell Value=1textNO
B11:F11Cell Value=3textNO
B11:F11Cell Value=2textNO
B11:F11Cell Value=1textNO
Cells with Data Validation
CellAllowCriteria
B10:F14List=$A$3:$A$5
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0
Try

Book2
ABCDEFGHIJKLMNO
1TRAINING MATRIX
2
3IN PROGRESS
4DROPPED
5FINISHED
6TRAINING 1TRAINING 2TRAINING 3TRAINING 4TRAINING 5IN PROGRESSDROPPEDFINISHED
7DEADLINE5/18/20235/15/20235/15/20236/10/20236/10/2023
8
9STAFFIN PROGRESS
10STAFF AIN PROGRESSIN PROGRESSIN PROGRESSFINISHEDFINISHED302STAFF CTRAINING 1
11STAFF BDROPPEDIN PROGRESSFINISHEDFINISHEDFINISHED113TRAINING 5
12STAFF CIN PROGRESSFINISHEDDROPPEDFINISHEDIN PROGRESS212 
13STAFF DDROPPEDFINISHEDFINISHEDIN PROGRESSFINISHED113 
14STAFF EFINISHEDFINISHEDIN PROGRESSIN PROGRESSDROPPED212
15
16
17
18
Sheet1
Cell Formulas
RangeFormula
H10:H14H10=COUNTIF(B10:F10,"IN PROGRESS")
I10:I14I10=COUNTIF(B10:F10,"DROPPED")
J10:J14J10=COUNTIF(B10:F10,"FINISHED")
N10:N13N10=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))),"")
 
Upvote 0
Solution
Try

Book2
ABCDEFGHIJKLMNO
1TRAINING MATRIX
2
3IN PROGRESS
4DROPPED
5FINISHED
6TRAINING 1TRAINING 2TRAINING 3TRAINING 4TRAINING 5IN PROGRESSDROPPEDFINISHED
7DEADLINE5/18/20235/15/20235/15/20236/10/20236/10/2023
8
9STAFFIN PROGRESS
10STAFF AIN PROGRESSIN PROGRESSIN PROGRESSFINISHEDFINISHED302STAFF CTRAINING 1
11STAFF BDROPPEDIN PROGRESSFINISHEDFINISHEDFINISHED113TRAINING 5
12STAFF CIN PROGRESSFINISHEDDROPPEDFINISHEDIN PROGRESS212 
13STAFF DDROPPEDFINISHEDFINISHEDIN PROGRESSFINISHED113 
14STAFF EFINISHEDFINISHEDIN PROGRESSIN PROGRESSDROPPED212
15
16
17
18
Sheet1
Cell Formulas
RangeFormula
H10:H14H10=COUNTIF(B10:F10,"IN PROGRESS")
I10:I14I10=COUNTIF(B10:F10,"DROPPED")
J10:J14J10=COUNTIF(B10:F10,"FINISHED")
N10:N13N10=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.
 
Upvote 0
You're welcome
Glad I could help.

Have a look at

 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top