Excel - Use the IF statement with Data Validation

Ask a question



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

Macro to Move or Copy Data Between Excel Workbooks
Excel - A macro to transfer data from rows to columns