RESOLVED :: Excel IF statement with Data Validation

[aronyo]

Hello,

I have a simple work book. Please refer to the attachment Book1.xls.

In B1, the data is limited (by Data Validation with a drop down List) to Yes and No.

I need to limit the range of data in D1 as, If B1 = Yes , then the value of D1 could be entered More or Equal to 51 ; and if B1 = No then the data entered into D1 should be Less than or Equals to 50

I prefer to do it with Data Validation. Please help.

[DonkeyOte]

Your data validation formula for D2 would be:

=OR(AND(B2="Yes",D2>=51),AND(B2="No",D2<=50))

That said there is nothing to stop user selecting No in B2, entering 23 into D2, then retrospectively selecting Yes in B2... the 23 would remain even though invalid...

[Palmetto]

I would use the Whole Number or Decimal option for Data Validation to ensure the entries are restricted to numbers.

Minimum formula:
=IF(B2="Yes",51,IF(B2="No",0))

Maximum formula
=IF(B2="Yes",1+E24,IF(B2="No",50))


Note: 1+E24 is exponential notation for a very large number. You can just as easily change this a 10,000, or whatever maximum number you need.

DKO is correct about users being able to change the selection. Cells with Data Validation do not update for manual entries.

[aronyo]

Thanks a lot both DKO & Palmetto .. both are working but there is still a problem appearing in both cases.

If I select 'Yes' in B2 and enter say 100 into D2, and then again change the B2 into 'No', there is no error message appearing. Even for the above case after selecting 'No' at B2, if I select the D2 (with a value 100), and hit enter, no error message is appearing.

Can you please help me once again to solve the problem completely ?

[DonkeyOte]

This was exactly the point I made in my response... you will need to use VBA if you want to override existing values upon change of value in B2, ie along the lines of:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim boolOK As Boolean
If Target.Count > 1 Or Target.Address <> "$B$2" Then Exit Sub
Select Case Val(Target.Offset(, 2).Value)
    Case Is <= 50
        boolOK = UCase(Target.Value) = "NO"
    Case Else
        boolOK = UCase(Target.Value) = "YES"
End Select
Application.EnableEvents = False
If Not boolOK Then Target.Offset(, 2).Value = ""
Application.EnableEvents = True
End Sub

To insert the above - right click on tab against which code is to be applied - select View Code - paste above into resulting window (thereafter ensure macros are enabled when using the file)

[aronyo]

!!!!! THANKS A LOT ... DKO !!!!! its working ...

last help ..

I need the same thing for C4:C34 (yes/no) with F4:F34 (value) ..

Sorry for bothering you so many times, but will you , please ???

[DonkeyOte]

Assuming the validation rules are the same for C4:C34 / F4:F34 as for B2/D2 then see below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim boolOK As Boolean
If Target.Count > 1 Or Intersect(Target, Range("B2,C4:C34")) Is Nothing Then Exit Sub
Select Case Val(Target.Offset(, 2 - (Target.Column = 3)).Value)
    Case Is <= 50
        boolOK = UCase(Target.Value) = "NO"
    Case Else
        boolOK = UCase(Target.Value) = "YES"
End Select
Application.EnableEvents = False
If Not boolOK Then Target.Offset(, 2 - (Target.Column = 3)).Value = ""
Application.EnableEvents = True
End Sub

[aronyo]

llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll
llllllllllllllllllllllllll THANKS llllllllllllllllllllllllll
llllllllllllllllllllllllll DKO llllllllllllllllllllllllll
llllllllllllllllllllllllll IT'S llllllllllllllllllllllllll
llllllllllllllllllllllllll WORKING llllllllllllllllllllllllll
llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll

Thanks a lot Bro ..