Excel - Use the IF statement with Data Validation

December 2016




Issue


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>

Solution


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.

Related :

This document entitled « Excel - Use the IF statement with Data Validation » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.