A few words of thanks would be greatly appreciated.

Excel - Use the IF statement with Data Validation


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.</gras>


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 of 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

Note that

Thanks to venkat1926 for this tip on the forum.

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled "Excel - Use the IF statement with Data Validation," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).