0
Thanks

A few words of thanks would be greatly appreciated.

Excel - Use the IF statement with Data Validation




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.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
Jean-François Pillou

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.

Learn more about the CCM team

Published by . Latest update on 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 (https://ccm.net/).

0 Comments