Previous Topic Next Topic

Data Validation

Closed
Gagan - Apr 25, 2010 at 02:28 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 - Apr 25, 2010 at 08:03 PM
<
config>Windows XP / Internet Explorer 6.0</config

I have a drop down list with Yes and No in my excel sheet . I wand if some one chooses Yes,the next coloumn should give me '1' and if someone chooses No, the next coloum should give '0'

I need urgent help.....Please suggest !

1 reply

Answer 1 / 1
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Apr 25, 2010 at 08:03 PM
You need to define this function in the sheet in which you want to have this


Private Sub Worksheet_Change(ByVal Target As Range)


End Sub


Lets say your drop down is in column A, then you have some thing like this 

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column <> 1 Then Exit Sub
    
    If UCase(Target) = "YES" Then
        
        Target.Offset(0, 1) = 1
    
    ElseIf UCase(Target) = "NO" Then
        
        Target.Offset(0, 1) = 0
    
    Else
        
        Target.Offset(0, 1) = ""


    End If

End Sub
0