Formula IF, returns a value or show a list

Question

Hello all,&nbsp;I need to create a formula if, that returns "Not applicable" if value is "No", and need to show a dropdown list if value is "Yes".&nbsp;Coudln't find anything in Internet.&nbsp;Thanks in advance

hansvogelaar · Answer

OriolFontanals&nbsp;
See the attached sample workbook. You'll have to allow macros when you open it.

hansvogelaar · Answer

OriolFontanals&nbsp;
Right-click the sheet tab and select 'View Code' from the context menu.
You'll see the Worksheet_Change event procedure in the worksheet's code module.
This fills or clears B2.

oriolfontanals · Answer

Great Hans!Thanks what I was looking! Just one question, how did you make it, that when select "No" it directly shows the "Not applicable"? I tried to mimic it, but still have to select it from the dropdown box (with just Not applicable value").

oriolfontanals · Answer

HansVogelaar&nbsp;Understood! I really appreciate your help Hans!And if I don't want to have just for a single cell, but for a whole column?I tried the attached to create a range, but did not work...&nbsp;Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("d4:d50"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Range("d4:d50").Value = "No" Then
            Range("e4:e50").Value = "Not Applicable"
        Else
            Range("e4:e50").ClearContents
        End If
        Application.EnableEvents = True
    End If
End Sub

harun24hr · Answer

OriolFontanals&nbsp;To compare values of more than one cell you have to use loop. Try below codes.Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
    If Not Intersect(Range("D4:D50"), Target) Is Nothing Then
        Application.EnableEvents = False
        For Each rng In Range("D4:D50")
            If LCase(rng) = "no" Then
                rng.Offset(, 1) = "Not Applicable"
            Else
                rng.Offset(, 1).ClearContents
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

Forum Discussion

Formula IF, returns a value or show a list

Share

Resources