formula to indicate a cell based on other cell value

D

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.

[TABLE="width: 863"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Course 1
[/TD]
[TD="colspan: 2"]Course 2
[/TD]
[TD="colspan: 2"]Course 3
[/TD]
[TD="colspan: 2"]Course 4
[/TD]
[TD="colspan: 2"]Course 5
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee Code
[/TD]
[TD]T
[/TD]
[TD]A
[/TD]
[TD]T
[/TD]
[TD]A
[/TD]
[TD]T
[/TD]
[TD]A
[/TD]
[TD]T
[/TD]
[TD]A
[/TD]
[TD]T
[/TD]
[TD]A
[/TD]
[TD]T
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]90108226
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]70885239
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]90089568
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]90043158
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]60865961
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]60865986
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]60873999
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]70887266
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]90013032
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]90108226
[/TD]
[TD]Course 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]70885239
[/TD]
[TD]Course 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]90089568
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]90043158
[/TD]
[TD]Course 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]60865961
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]60865986
[/TD]
[TD]Course 2
[/TD]
[TD]Course 4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]60873999
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]70887266
[/TD]
[TD]Course 5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]90013032
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
G'day :grin:

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.

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[TD]
I​
[/TD]
[TD]
J​
[/TD]
[TD]
K​
[/TD]
[TD]
L​
[/TD]
[TD]
M​
[/TD]
[TD]
N​
[/TD]
[TD]
O​
[/TD]
[TD]
P​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
2 ​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Course 1​
[/TD]
[TD][/TD]
[TD]
Course 2​
[/TD]
[TD][/TD]
[TD]
Course 3​
[/TD]
[TD][/TD]
[TD]
Course 4​
[/TD]
[TD][/TD]
[TD]
Course 5​
[/TD]
[TD][/TD]
[TD]
Count​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
3 ​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2 ​
[/TD]
[TD]
-​
[/TD]
[TD]
3 ​
[/TD]
[TD]
1 ​
[/TD]
[TD]
1 ​
[/TD]
[TD]
1 ​
[/TD]
[TD]
3 ​
[/TD]
[TD]
1 ​
[/TD]
[TD]
1 ​
[/TD]
[TD]
1 ​
[/TD]
[TD]
10 ​
[/TD]
[TD]
4 ​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
4 ​
[/TD]
[TD][/TD]
[TD]Employee Code[/TD]
[TD]
T​
[/TD]
[TD]
A​
[/TD]
[TD]
T​
[/TD]
[TD]
A​
[/TD]
[TD]
T​
[/TD]
[TD]
A​
[/TD]
[TD]
T​
[/TD]
[TD]
A​
[/TD]
[TD]
T​
[/TD]
[TD]
A​
[/TD]
[TD]
T​
[/TD]
[TD]
A​
[/TD]
[TD]
T Index​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
5 ​
[/TD]
[TD][/TD]
[TD]
90108226​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
-​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
6 ​
[/TD]
[TD][/TD]
[TD]
70885239​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
-​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
7 ​
[/TD]
[TD][/TD]
[TD]
90089568​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
8 ​
[/TD]
[TD][/TD]
[TD]
90043158​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
3​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
9 ​
[/TD]
[TD][/TD]
[TD]
60865961​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
10 ​
[/TD]
[TD][/TD]
[TD]
60865986​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
-​
[/TD]
[TD]
4​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
11 ​
[/TD]
[TD][/TD]
[TD]
60873999​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
12 ​
[/TD]
[TD][/TD]
[TD]
70887266​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
-​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
13 ​
[/TD]
[TD][/TD]
[TD]
90013032​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
14 ​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
15 ​
[/TD]
[TD][/TD]
[TD]Outstanding Training Courses[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
10 ​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
16 ​
[/TD]
[TD][/TD]
[TD]
90108226​
[/TD]
[TD]
Course 1​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
Course 4​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
1 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
17 ​
[/TD]
[TD][/TD]
[TD]
70885239​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
Course 2​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
2 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
18 ​
[/TD]
[TD][/TD]
[TD]
90043158​
[/TD]
[TD]
Course 1​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
Course 3​
[/TD]
[TD][/TD]
[TD]
Course 4​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD]
3 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
19 ​
[/TD]
[TD][/TD]
[TD]
60865986​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
Course 2​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
Course 4​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
4 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
20 ​
[/TD]
[TD][/TD]
[TD]
70887266​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
Course 5​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
5 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
21 ​
[/TD]
[TD][/TD]
[TD]
90013032​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
Course 2​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
6 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
22 ​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
7 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
23 ​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
8 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
24 ​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
9 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
25 ​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD]
10 ​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: .[/TD]
[/TR]
</tbody>[/TABLE]

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'.

DHayes said:
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.
Click to expand...

Shouldn't this be 90043158?
 
Upvote 0
Try this.


[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
A
​
[/TD]
[TD]
B​
[/TD]
[TD]
C
​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[TD]
I​
[/TD]
[TD]
J​
[/TD]
[TD]
K​
[/TD]
[TD]
L​
[/TD]
[TD]
M​
[/TD]
[TD]
N​
[/TD]
[TD]
O​
[/TD]
[TD]
P​
[/TD]
[TD]
Q​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
2 ​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Course 1​
[/TD]
[TD][/TD]
[TD]
Course 2​
[/TD]
[TD][/TD]
[TD]
Course 3​
[/TD]
[TD][/TD]
[TD]
Course 4​
[/TD]
[TD][/TD]
[TD]
Course 5​
[/TD]
[TD][/TD]
[TD]
Count​
[/TD]
[TD]
Count​
[/TD]
[TD]
Count​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
3 ​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2 ​
[/TD]
[TD]
-​
[/TD]
[TD]
3 ​
[/TD]
[TD]
1 ​
[/TD]
[TD]
1 ​
[/TD]
[TD]
1 ​
[/TD]
[TD]
3 ​
[/TD]
[TD]
1 ​
[/TD]
[TD]
1 ​
[/TD]
[TD]
1 ​
[/TD]
[TD]
10 ​
[/TD]
[TD]
4 ​
[/TD]
[TD]
7 ​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
4 ​
[/TD]
[TD][/TD]
[TD]Employee Code[/TD]
[TD]
T​
[/TD]
[TD]
A​
[/TD]
[TD]
T​
[/TD]
[TD]
A​
[/TD]
[TD]
T​
[/TD]
[TD]
A​
[/TD]
[TD]
T​
[/TD]
[TD]
A​
[/TD]
[TD]
T​
[/TD]
[TD]
A​
[/TD]
[TD]
T​
[/TD]
[TD]
A​
[/TD]
[TD]
T to do​
[/TD]
[TD]
T Index​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
5 ​
[/TD]
[TD][/TD]
[TD]
90108226​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
-​
[/TD]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
6 ​
[/TD]
[TD][/TD]
[TD]
70885239​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
-​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
7 ​
[/TD]
[TD][/TD]
[TD]
90089568​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
8 ​
[/TD]
[TD][/TD]
[TD]
90043158
​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
3​
[/TD]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
9 ​
[/TD]
[TD][/TD]
[TD]
60865961
​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
10 ​
[/TD]
[TD][/TD]
[TD]
60865986​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
-​
[/TD]
[TD]
2​
[/TD]
[TD]
4​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
11 ​
[/TD]
[TD][/TD]
[TD]
60873999
​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
12 ​
[/TD]
[TD][/TD]
[TD]
70887266
​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
-​
[/TD]
[TD]
1​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
13 ​
[/TD]
[TD][/TD]
[TD]
90013032
​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
14 ​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
15 ​
[/TD]
[TD][/TD]
[TD]Outstanding Training Courses
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
7 ​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
16 ​
[/TD]
[TD][/TD]
[TD]
90108226​
[/TD]
[TD]
Course 1​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
Course 4​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
1 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
17 ​
[/TD]
[TD][/TD]
[TD]
70885239​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
Course 2​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
2 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
18 ​
[/TD]
[TD][/TD]
[TD]
90043158​
[/TD]
[TD]
Course 1​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
3 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
19 ​
[/TD]
[TD][/TD]
[TD]
60865986
​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
Course 2​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
Course 4​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
4 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
20 ​
[/TD]
[TD][/TD]
[TD]
70887266
​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
Course 5​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
5 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
21 ​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
6 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
22 ​
[/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
7 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
23 ​
[/TD]
[TD][/TD]
[TD]
-
​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
8 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
24 ​
[/TD]
[TD][/TD]
[TD]
-
​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
9 ​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]
25 ​
[/TD]
[TD][/TD]
[TD]
-
​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD]
-​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
-​
[/TD]
[TD]
10 ​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: .
[/TD]
[/TR]
</tbody>[/TABLE]

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.:-?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Morpheus2022
  • Question Question
Training Needs Analysis Formula - Awesome (If I can crack this problem)
Replies
2
Views
702
awoohaw
awoohaw
F
  • Question Question
Excel Macro Assistance Needed for Staff Roster and Training Allocation
Replies
0
Views
451
FajnaAli
F
M
  • Question Question
Formula to calculate team attendance %
Replies
7
Views
926
Fluff
Fluff
Morpheus2022
  • Solved
Training Needs Analysis
Replies
6
Views
656
Kerryx
K
K
  • Solved
Conditionally sum fractional cells based on column headings
Replies
5
Views
160
Cubist
Cubist

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

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