formula to indicate a cell based on other cell value

DHayes

Board Regular
Joined
Nov 12, 2014
Messages
244
Good day,
I am looking for a formula to indicate what training a staff member still has to attend. Below is the training schedule. The T indicates the training they need to attend. The A indicates they have attended the training. What I would like is the list of the staff member that still has to attend training.
below example.

Course 1
Course 2
Course 3
Course 4
Course 5
2
0
3
1
1
1
3
1
1
1
Employee Code
T
A
T
A
T
A
T
A
T
A
T
A
90108226
1





1



2
0
70885239


1







1
0
90089568










0
0
90043158
1



1
1
1
1


3
2
60865961










0
0
60865986


1



1



2
0
60873999










0
0
70887266








1

1
0
90013032


1
1





1
1
2
Result
90108226
Course 1
70885239
Course 2
90089568
90043158
Course 1
60865961
60865986
Course 2
Course 4
60873999
70887266
Course 5
90013032

<tbody>
</tbody>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
G'day :biggrin:

I've not got quite what you've shown in your post as the desired end result, but am close.
BTW, I got a different result than you for several employees (90108226, 90043158, 90013032)? :confused:


I added a helper column (in P) to your first table in order to ascertain only those employees who had outstanding training and provide an index to allow them to be looked up in order.

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
2
Course 1
Course 2
Course 3
Course 4
Course 5
Count
3
2
-
3
1
1
1
3
1
1
1
10
4
4
Employee Code
T
A
T
A
T
A
T
A
T
A
T
A
T Index
5
90108226​
1​
1​
2​
-​
1​
6
70885239​
1​
1​
-​
2​
7
90089568​
-​
-​
-​
8
90043158​
1​
1​
1​
1​
1​
3​
2​
3​
9
60865961​
-​
-​
-​
10
60865986​
1​
1​
2​
-​
4​
11
60873999​
-​
-​
-​
12
70887266​
1​
1​
-​
5​
13
90013032​
1​
1​
1​
1​
2​
6​
14
15
Outstanding Training Courses
10
16
90108226​
Course 1​
-​
-​
Course 4​
-​
2​
1
17
70885239​
-​
Course 2​
-​
-​
-​
1​
2
18
90043158​
Course 1​
-​
Course 3​
Course 4​
-​
3​
3
19
60865986​
-​
Course 2​
-​
Course 4​
-​
2​
4
20
70887266​
-​
-​
-​
-​
Course 5​
1​
5
21
90013032​
-​
Course 2​
-​
-​
-​
1​
6
22
-​
-​
-​
-​
-​
-​
-​
7
23
-​
-​
-​
-​
-​
-​
-​
8
24
-​
-​
-​
-​
-​
-​
-​
9
25
-​
-​
-​
-​
-​
-​
-​
10

<tbody>
</tbody>
Sheet: .

<tbody>
</tbody>

P5 = IF( $N5 > 0, COUNTIFS( $N$5:$N5, ">0" ), 0 )
C16 = IFERROR( INDEX( $C$5:$C$13, MATCH( P16, $P$5:$P$13, 0 ) ), "-" )
D16 = IF( IFERROR( INDEX( D$5:D$13, MATCH( $C16, $C$5:$C$13, 0 ) ), 0 ), D$2, "-" )
N16 = COUNTIFS( $D16:$M16, "Course*" )

Does this do the job well enough for you?
 
Upvote 0
good day Col Delane,
It is almost there. If you look at 90089568 This person has a T and A for courses 3 & 4. The result I would like to get is only course 1. In this scenario they were allocated the training but has not attended it.
So in a nutshell I only want to know who has been allocated training and has not attended.
I hope it is a little bit clearer.
 
Upvote 0
Ooops - I forgot about that condition! :oops: I'll keep workin'.

If you look at 90089568 This person has a T and A for courses 3 & 4. The result I would like to get is only course 1. In this scenario they were allocated the training but has not attended it.

Shouldn't this be 90043158?
 
Upvote 0
ADVERTISEMENT
My Apologies that is correct.
 
Upvote 0
Try this.


A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
2
Course 1
Course 2
Course 3
Course 4
Course 5
Count
Count
Count
3
2
-
3
1
1
1
3
1
1
1
10
4
7
4
Employee Code
T
A
T
A
T
A
T
A
T
A
T
A
T to do
T Index
5
90108226​
1​
1​
2​
-​
2​
1​
6
70885239​
1​
1​
-​
1​
2​
7
90089568​
-​
-​
-​
-​
8
90043158
1​
1​
1​
1​
1​
3​
2​
1​
3​
9
60865961
-​
-​
-​
-​
10
60865986​
1​
1​
2​
-​
2​
4​
11
60873999
-​
-​
-​
-​
12
70887266
1​
1​
-​
1​
5​
13
90013032
1​
1​
1​
1​
2​
-​
-​
14
15
Outstanding Training Courses
7
16
90108226​
Course 1​
-​
-​
-​
-​
-​
Course 4​
-​
-​
-​
2​
1
17
70885239​
-​
-​
Course 2​
-​
-​
-​
-​
-​
-​
-​
1​
2
18
90043158​
Course 1​
-​
-​
-​
-​
-​
-​
-​
-​
-​
1​
3
19
60865986
-​
-​
Course 2​
-​
-​
-​
Course 4​
-​
-​
-​
2​
4
20
70887266
-​
-​
-​
-​
-​
-​
-​
-​
Course 5​
-​
1​
5
21
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
6
22
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
7
23
-
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
8
24
-
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
9
25
-
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
-​
10

<tbody>
</tbody>
Sheet: .

<tbody>
</tbody>

Data Table helper columns
New column at P ("T to do") to exclude any courses both scheduled and attended, as well as those attended when not scheduled (like 90013032 Course 5):
P5 = SUM( MAX(0, D5 - E5), MAX( 0, F5 - G5 ), MAX(0, H5 - I5 ), MAX(0, J5 - K5), MAX( 0, L5 - M5 ) )

"T Index" formula in Q modified to now reference new column P "T to do" rather than N:
Q5 = IF( $P5 > 0, COUNTIFS( $P$5:$P5, ">0" ), 0 )

Outstanding Training Courses
"Employee Code" formula in C16:C25 modified to now reference new column Q rather than P:
C16 = IFERROR( INDEX( $C$5:$C$13, MATCH( Q16, $Q$5:$Q$13, 0 ) ), "-" )

Modified result formulas in C16:M25 now a bit clunky through using OFFSET (from home base at row 4) to locate the correct employee/course combos ('cos I couldn't work out how to return a two cell range for T & the adjacent A using INDEX/MATCH!!!):
D16 = IF( AND( $C16 <> "-", D$4 = "T", IFERROR( SUM( OFFSET( D$4, MATCH( $C16, $C$5:$C$13, 0 ), 0 ) - OFFSET( D$4, MATCH( $C16, $C$5:$C$13, 0 ), 1 ) ) = 1, FALSE )), D$2, "-" )

Check count calc. of courses to be completed by employees shifted from N to P to align with the helper calc. in the Data Table.

I think I've got it this time.:confused:
 
Upvote 0

Forum statistics

Threads
1,196,516
Messages
6,015,677
Members
441,915
Latest member
sm Hussaini

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