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)
Thanks to venkat1926
for this tip on the forum.
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