Formula Not working for If with And

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)
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/15INORIENTATION TO ENFORCEMENT26ACEE, JO6260ASEENF4VAN 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, JO6260ASEENF4CURRENTVAN NUYSN/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>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Maybe this
Code:
=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.
 
Upvote 0
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","")
 
Upvote 0
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","")
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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