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

ChrisOK

Well-known Member
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
ABCDEFG
1
2NAMEJOHNJONES
3BEMS12333
5MGRJerryMacDonald
61Q
7CategoryCourseNameCourse#JanFebMar
8MANDATORYComputingSecurityE1000100
9MANDATORYEthics1E1001010
10MANDATORYEthics2E1002010
11MANDATORYEthics3CS100000
12MANDATORY
JONES

Part 2 ( posted below )

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

ChrisOK

Well-known Member
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
ABCDEFGHIJKL
1MANDATORYCOURSES
2ComputingSecurityEthics1Ethics2Ethics3TotalMandatory
3NAMEBEMSDEPTMGRLOCPEPDPCS100E1000E1001E10024
5JohnJones1298648BETMacDonaldNYC10/5/200311/1/2003Jan-28-04Jan-31-04Feb-04-043
6MarySmith1723471BETMacDonaldNYC10/6/2003Jan-30-04Jan-31-04Feb-10-043
7SallyFox1587762SCMHughsOKC10/7/200311/3/20030
8JimColeman284571BETMacDonaldNYC10/8/200311/4/2003Jan-28-04Feb-01-04Feb-12-043
9HarveyHart864721ENGINEERINGSmartSEA10/8/200311/4/2003Jan-22-04Jan-15-042
10EllenHollis365214ENGINEERINGSmartSEA10/8/200311/4/2003Jan-24-04Jan-18-04Feb-08-043
110
120
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

Board Regular
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

Board Regular
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 ... <> "") )

MrExcel MVP
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

Well-known Member
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!!

Replies
0
Views
333
Replies
0
Views
177
Replies
12
Views
330
Replies
6
Views
352
Replies
2
Views
269

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,102
Messages
5,768,102
Members
425,454
Latest member
khoro

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?

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

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