Excel IF statement with Data Validation

Aronyo - Aug 7, 2009 at 01:44 PM
 RayH - Nov 17, 2010 at 09:56 PM

I have a simple work book.
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 A1 = Yes , then the value of D1 could be entered More or Equal to 51 ; and if A1 = No then the data entered into D1 should be Less than or Equals to 50

I prefer to do it with Data Validation. Anyone please help.

4 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Aug 8, 2009 at 09:13 PM
I wondear wheher any quantitative condtion can be sued in validation.

however you can haae an event code which may solve your problem

You know how to create validation in A1 with "yes" or "no"

right click sheet tab and click view code
and in that window copy paste this event code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
If Target = "" Then Exit Sub
If Target.Offset(0, -1) = "yes" And Target < 51 Then

MsgBox "you should enter a number more than or equel to 51"
End If
If Target.Offset(0, -1) = "no" And Target > 50 Then
MsgBox "you should enter number less than or equal to 50"

End If

End Sub

now choose "yes" in A1 and type in B1 some number less than 51 see what happens.
similarly choose "no" for A1 and type in B1 some number more than 50 what happens

if the correct number is entered nothing will happen.