Forum Discussion
Formula IF, returns a value or show a list
Hello all, 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". Coudln't find anything in Internet. Thanks in...
See the attached sample workbook. You'll have to allow macros when you open it.
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").
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").
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.
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...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 SubOriolFontanals 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
