that_one_girl
New Member
- Joined
- Mar 22, 2017
- Messages
- 43
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)
<tbody>
</tbody>
CURRENT WORKBOOK FIELDS:
<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col></colgroup>
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)
DATE (A) | IN/OUT (B) | SUBJECT (C) | HOURS (D) | NAME (E) | CSHO ID (F) | CLASS (G) | UNIT (H) | REGION (I) | OFFICE LOCATION (J) |
4/10/15 | IN | ORIENTATION TO ENFORCEMENT | 26 | ACEE, J | O6260 | ASE | ENF | 4 | VAN NUYS |
<tbody>
</tbody>
CURRENT WORKBOOK FIELDS:
NAME (A) | CSHO/EE ID (B) | CLASS (C) | UNIT (D) | REGION (E) | STATE STATUS (F) | OFFICE (G) | HIRE DATE (H) | TRAINING STATUS (I) | ORIENTATION TO ENFORCEMENT (J) |
ACEE, J | O6260 | ASE | ENF | 4 | CURRENT | VAN NUYS | N/A | =IF(AND('[DOSHTrainingRecordsVer.03-AL.xlsm]ALL RECORDS'!$E:$E=A7,'[DOSHTrainingRecordsVer.03-AL.xlsm]ALL RECORDS'!$C:$C="ORIENTATION TO ENFORCEMENT"),"COMPLETED","") |
<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col></colgroup>