Excel - Use the IF statement with Data Validation

May 2017




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


Published by aakai1056. Latest update on December 1, 2011 at 09:44 AM by aakai1056.
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 (ccm.net).