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>
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,261
Office Version
2013
Platform
Windows
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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,749
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

New Member
Joined
Mar 22, 2017
Messages
43
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","")
 

Forum statistics

Threads
1,081,677
Messages
5,360,451
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top