Formula Not working for If with And

[that_one_girl]

My formula isn't working, and I can't tell why! It's frustrating the crap out of me! Can anyone help?

What I'm trying to do: Get the cell to state "COMPLETED" if the person has completed a class.

What I'm using
Current Workbook
Training Records workbook
Cell A7 (employee name) in current workbook
I want it to check the employee name in A7 against the names in column E of the Training Records workbook, if it finds the name, check the Subject in column C for "ORIENTATION TO ENFORCEMENT". If the employees name has Orientation to Enforcement next to it, I want it to read "COMPLETED", if they have not taken that training, then leave it blank.

Am I entering something wrong in my formula below?

=IF(AND('[DOSHTrainingRecordsVer.03-AL.xlsm]ALL RECORDS'!$E:$E=A7,'[DOSHTrainingRecordsVer.03-AL.xlsm]ALL RECORDS'!$C:$C="ORIENTATION TO ENFORCEMENT"),"COMPLETED","")



REFERENCE BOOKFILEDS (Workbook titled: TrainingRecordsVer.03-AL and information is on ALL RECORDS tab)
[TABLE="width: 1095"]
<tbody>[TR]
[TD]DATE
(A)[/TD]
[TD]IN/OUT
(B)[/TD]
[TD]SUBJECT
(C) [/TD]
[TD]HOURS
(D)[/TD]
[TD]NAME
(E)[/TD]
[TD]CSHO ID
(F)[/TD]
[TD]CLASS
(G)[/TD]
[TD]UNIT
(H)[/TD]
[TD]REGION
(I)[/TD]
[TD]OFFICE LOCATION
(J)[/TD]
[/TR]
[TR]
[TD]4/10/15[/TD]
[TD]IN[/TD]
[TD]ORIENTATION TO ENFORCEMENT[/TD]
[TD]26[/TD]
[TD]ACEE, J[/TD]
[TD]O6260[/TD]
[TD]ASE[/TD]
[TD]ENF[/TD]
[TD]4[/TD]
[TD]VAN NUYS[/TD]
[/TR]
</tbody>[/TABLE]




CURRENT WORKBOOK FIELDS:

[TABLE="width: 1095"]
<tbody>[TR]
[TD]NAME
(A)[/TD]
[TD]CSHO/EE ID (B)[/TD]
[TD]CLASS (C) [/TD]
[TD]UNIT
(D)[/TD]
[TD]REGION
(E)[/TD]
[TD]STATE STATUS
(F)[/TD]
[TD]OFFICE
(G)[/TD]
[TD]HIRE DATE
(H)[/TD]
[TD]TRAINING STATUS
(I)[/TD]
[TD]ORIENTATION TO ENFORCEMENT
(J)[/TD]
[/TR]
[TR]
[TD]ACEE, J[/TD]
[TD]O6260[/TD]
[TD]ASE[/TD]
[TD]ENF[/TD]
[TD]4[/TD]
[TD]CURRENT[/TD]
[TD]VAN NUYS[/TD]
[TD]N/A[/TD]
[TD] [/TD]
[TD] =IF(AND('[DOSHTrainingRecordsVer.03-AL.xlsm]ALL RECORDS'!$E:$E=A7,'[DOSHTrainingRecordsVer.03-AL.xlsm]ALL RECORDS'!$C:$C="ORIENTATION TO ENFORCEMENT"),"COMPLETED","")

[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[JLGWhiz]

Maybe this

=IF(AND('[DOSHTrainingRecordsVer.03-AL.xlsm]ALL RECORDS'!$E2=A7,'[DOSHTrainingRecordsVer.03-AL.xlsm]ALL RECORDS'!$C2="ORIENTATION TO ENFORCEMENT"),"COMPLETED","")

The formula would need to be entered on each row you want to evaluate, starting at J2. The $E:$E and $C:$C represent the entire column and are not applicable to an IF(AND()) function.

 

[pgc01]

Hi

If I understand correctly a solution is to replace And() by COUNTIFS()

=IF(COUNTIFS('[DOSHTrainingRecordsVer.03-AL.xlsm]ALL RECORDS'!$E:$E,A7,'[DOSHTrainingRecordsVer.03-AL.xlsm]ALL RECORDS'!$C:$C,"ORIENTATION TO ENFORCEMENT"),"COMPLETED","")

 

[that_one_girl]

THIS WORKED PERFECTLY! Thank you!!

Hi

If I understand correctly a solution is to replace And() by COUNTIFS()

=IF(COUNTIFS('[DOSHTrainingRecordsVer.03-AL.xlsm]ALL RECORDS'!$E:$E,A7,'[DOSHTrainingRecordsVer.03-AL.xlsm]ALL RECORDS'!$C:$C,"ORIENTATION TO ENFORCEMENT"),"COMPLETED","")

 

[pgc01]

You're welcome.