IF, THEN place a one based on month or zero if no class

[ChrisOK]

I've got a problem trying to figure out what function to use to pull the result from one sheet to another that shows:

It's sort of an IF, THEN scenario.....

IF John Jones took the Computing Security Class
THEN place a 1 in cell E8 of his personal training tracking sheet: "JONES" because it sees that he TOOK THAT CLASS in the MONTH OF JANUARY...
If he'd taken it in February - then the 1 would appear in COL F of his "JONES" tab...
If he's not taken it at all ....then zeros would appear in COL E, F and G

E,F and G of Row 12 of the JONES tab remains blank with no zeros or ones because no class name is housed in Column C to track.....

Book2
	A	B	C	D	E	F	G
1
2		NAME	JOHNJONES
3		BEMS	12333
4		DEPT	BusinessExcellence
5		MGR	JerryMacDonald
6					1Q
7		Category	CourseName	Course#	Jan	Feb	Mar
8		MANDATORY	ComputingSecurity	E1000	1	0	0
9		MANDATORY	Ethics1	E1001	0	1	0
10		MANDATORY	Ethics2	E1002	0	1	0
11		MANDATORY	Ethics3	CS100	0	0	0
12		MANDATORY
13		BUSINESSUNIT&LAS/R	Diversity	D1000
14		BUSINESSUNIT&LAS/R	Leadership2004	L2004
15		BUSINESSUNIT&LAS/R	LaptopSecurity	L2005
16		BUSINESSUNIT&LAS/R
17		BUSINESSUNIT&LAS/R
18		BUSINESSUNIT&LAS/R
19		BUSINESSUNIT&LAS/R
JONES

Part 2 ( posted below )

 

[ChrisOK]

Here's the Training MASTER sheet that the personal tab for "JONES" LOOKS TO....to verify whether or not he is compliant and took the training or not....and if not -- it turns red and alerts the training administrator.

As you can see, according to this MASTER tab -- JONES has taken the class in COL H, I and J - but has not taken K....
The personal tab for "JONES" should look to the master and plug in a '1' if it sees that he's taken the course.... or a 0 if he has not....
Then, I'll hide the zeros and conditionally format them to turn red.
If no course is entered on a row such as row 12 on the JONES tab-- then no zeros should appear until a class is entered....

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1								MANDATORYCOURSES
2								ComputingSecurity	Ethics1	Ethics2	Ethics3	TotalMandatory
3	NAME	BEMS	DEPT	MGR	LOC	PE	PDP	CS100	E1000	E1001	E1002	4
4	COMPLETIONDEADLINE							1/31/2004	2/1/2004	2/15/2004	7/1/2004
5	JohnJones	1298648	BET	MacDonald	NYC	10/5/2003	11/1/2003	Jan-28-04	Jan-31-04	Feb-04-04		3
6	MarySmith	1723471	BET	MacDonald	NYC	10/6/2003		Jan-30-04	Jan-31-04	Feb-10-04		3
7	SallyFox	1587762	SCM	Hughs	OKC	10/7/2003	11/3/2003					0
8	JimColeman	284571	BET	MacDonald	NYC	10/8/2003	11/4/2003	Jan-28-04	Feb-01-04	Feb-12-04		3
9	HarveyHart	864721	ENGINEERING	Smart	SEA	10/8/2003	11/4/2003	Jan-22-04	Jan-15-04			2
10	EllenHollis	365214	ENGINEERING	Smart	SEA	10/8/2003	11/4/2003	Jan-24-04	Jan-18-04	Feb-08-04		3
11												0
12												0
Training MASTER

I'm thinking that something like this -- might work -- using Yes and No
based on if it sees "JAN" within the date or "FEB" within the date on the MASTER tab.....( because it can cross match the JAN or FEB headers on the personal JONES tab...
The below ( does not ) work - but something similar might - if anyone has any ideas???

=IF(--(TEXT(INDEX(MTable,0,8),"mmm")=E$7,"YES")),--(INDEX(MTable,0,1)=$C$2))

I have defined a Named Range on the Master called MTable

 

[grahamj]

Nobody had a go yet? I'll give it a try.

You want to match the name in column A on the Master with the name in C2 on the personal sheet.
And the course in row 2 on the Master with the course in column C on the personal sheet.
The intersection of these (on the Master) gives a date.

You want the month name of this date to match the month name in row 7 on the personal sheet.
And get a True of False in the format 1 or 0.

E8 =--(TEXT(date-value,"mmm")=E$7)

And date-value is from
INDEX(date-range,match-row,match-column)

Date-range is $H$5:$K$10 (or a named range) on the Master.

Match-row is
MATCH($C$2,'Training Master'!$A$5:$A$10,0)
(or a named range again)

Match-column is
MATCH($C8,'Training Master'!$H$2:$K$2,0)
(note the $C8, not $C$8, as it is different on each row)
But I notice that Jones has "Ethics1" and Master has "Ethics 1".
I was trying to match them, and they'll need to be the same.
(The course numbers are a bit offset on Jones, also.)

So you get, for a first stab (without dealing with blanks yet):

=--( TEXT( INDEX( $H$5:$K$10, MATCH( $C$2, 'Training Master'!$A$5:$A$10,0), MATCH( $C8, 'Training Master'!$H$2:$K$2,0)), "mmm") = E$7)

Perhaps I'll think about the blanks.

 

[grahamj]

I can't really think of another way of checking for blank dates without repeating that whole INDEX( ... MATCH( ), MATCH( ) ) bit.


=( TEXT( INDEX( $H$5:$K$10, MATCH( $C$2, 'Training Master'!$A$5:$A$10,0), MATCH( $C8, 'Training Master'!$H$2:$K$2,0)), "mmm") = E$7) * ( INDEX( $H$5:$K$10, MATCH( $C$2, 'Training Master'!$A$5:$A$10,0), MATCH( $C8, 'Training Master'!$H$2:$K$2,0)) <> "")

And then, to check if the course is not entered on the row:

=IF( $C8="", "", ( TEXT etc ... <> "") )

 

[Aladin Akyurek]

Course# on JONES do not match Course# on MASTER. After correcting them on JONES, try the following:

In G8 on JONES enter, copy across then down:

=SUMPRODUCT(--(INDEX(MTable,0,MATCH($D8,'Training MASTER'!$A$3:$L$3,0))<>""),--(TEXT(INDEX(MTable,0,MATCH($D8,'Training MASTER'!$A$3:$L$3,0)),"mmm")=E$7),--(INDEX(MTable,0,1)=$C$2))

The month test occurs by 3-letter month names. Not quite sure whether this formula fits your needs. Maybe you should rethink whether real dates are needed in row 7 starting with E7. That would require changes to the forgoing formula.

 

[ChrisOK]

I agree - it's a little odd -- but this is what the training focal requested...
They wanted the ability to plug in the exact date that the employee completed a class....

Then have the ability to see it in an "at-a-glance" format to look back and see which quarter -- did that employee finish their course assignment and whether or not it was within the COMPLIANCE Requirement date zone....

The ones and zeros will be hidden with conditional format -- and simply show a green block -- when a '1' appears...
This way, the mgr can quickly look for the green blocks and know which month & quarter the training occurred...
Then, if he wants detail -- he can go to the other tab and look up exact dates....

Yes, I agree, the Master tab shows all -- but again, the focal wanted to have everyone together in one view -- and then strip away each person on their own tabs -- so they could print an "Employee Overview Report" to place in each employee's file -- and something for their mgrs to view and make notes upon individually...

THANKS SO MUCH FOR YOUR HELP!!!
YOU GUYS ARE AWESOME!!