Excel IF statement with Data ValidationClosed

Question

Hello,

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.

venkat1926 · Accepted Answer

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"
Target.Clear
End If
If Target.Offset(0, -1) = "no" And Target > 50 Then
MsgBox "you should enter number less than or equal to 50"
Target.Clear

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.

try.

Rob · Answer

Did you ever find a cure for this?  If I have two columns of data in A and B, and then a dropdown in C1 that lists all of column A...  If I select the value in A1 in C1, I want D1 to show the value in B1.  I think this is what you're trying to do also.

venkat1926 · Answer

Immediately I am not able to think of a formula solution. However you can use an event code

A1 down and B1 down has data. C1 has validation of list of column A data.

after copying the event code below at the appropriate place when you choose a value ofr C1 FROM VALIDATION then automatically corresponding B value will come up in D1.



right click the sheet tab and click view code. in that window that comes up copy paste this event code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cfind As Range, x As Variant
If Target.Address <> "$C$1" Then Exit Sub
x = Target.Value
Set cfind = Columns("A:A").Cells.Find(what:=x, lookat:=xlWhole, LookIn:=xlValues)
Target.Offset(0, 1) = cfind.Offset(0, 1)
End Sub

sunny · Answer

Select the cells in column D Till you want to apply the rules,
Go to data Validation,Select Custom from allow menu, then enter the formula
=if((A2:A11)="yes",D2:D11>=51,D2:D11<=50)
Here I have assume that data Validation list in column A apply from A2:A11, So I have include the range as D2:D11,change it as per your need.

Excel IF statement with Data Validation

4 responses

Similar discussions

Subscribe To Our Newsletter!