Using IF Statement to return Yes or No depending on info in 2 cells

[MrCat]

Hello,

I have a document with two columns (D and L) with information, one (D) cell says either Enabled, or Disabled; the other (L) is listed either as a blank cell, or with a date.

If D is Enabled, L should always be blank, and if D is Disabled then L should have a date value. I have been scanning forums and trying different methods to work this out, but to no avail.

I've tried to mix and match a few attempts, and my latest err'd formula is:

=IF(ISNUMBER(L2),D2="DISABLED","Yes",ISBLANK(L2),D2="ENABLED","Yes")

Others I have tried are:

=IF(D2="DISABLED",L2="","Yes")AND(D2="ENABLED",L2=NOTBLANK,"Yes")

By separating them:

=IF(ISNUMBER(L2),D2="DISABLED","Yes")

=IF(ISBLANK(L2),D2="ENABLED","Yes")


I know I'm completely off mark here, but would appreciate some help

 

[Jonmo1]

Try

=IF((D2="Enabled")+ISNUMBER(L2)=1,"Yes","No")

 

[Norie]

What's the logic for returning Yes or No?

 

[MrCat]

What's the logic for returning Yes or No?

I need it to show if the values are incorrectly placed together, so if there is a cell with Disabled but no date then that needs to show up, I'll be using some conditional formatting to highlight the cells if they show up as Yes this is correct, or No this is not correct

 

[MrCat]

Try

=IF((D2="Enabled")+ISNUMBER(L2)=1,"Yes","No")

This works how I want it, thank you Jonmo1!

Can you break down what ' +ISNUMBER(L7)=1 ' is doing in the formula please?

 

[Jonmo1]

I read it as 2 True/False statements.
(D2="Enabled") --> Does D2 = Enabled
ISNUMBER(L2) --> Does L2 contain a number?

When you apply a math operation (like + for addition), True becomes 1, False becomes 0
So we're adding 1's and 0's based on those questions.

You want Yes when only 1 of the 2 statements is true, either one.
You didn't actually say what you wanted otherwise, so I just assumed you would want No.
If BOTH are True, TRUE+TRUE = 2
If BOTH are False, FALSE+FALSE = 0

Only if 1 is true, and the other is false do you get 1
FALSE+TRUE = 1
TRUE+FALSE = 1

Hope that helps.

 

[MrCat]

Brilliant, thanks for being so clear!