Forum Discussion

Ghostgirl's avatar
Ghostgirl
Copper Contributor
Dec 08, 2021

Drop down list inside if statement

Good morning everyone,   Iam trying to create sth like a guide on whether or not an employee is eligible to receive end of service award based on certain selections. File is attached.   I want C9...
excel
Formulas and Functions
Ghostgirl's avatar
Ghostgirl
Copper Contributor
Dec 13, 2021

Dear Bosinander,

Thank you very much, static array formulas (and even non static array ones) are new to me, and I thank you for letting me know they can help here. However, when I try it for employee A, it works when I select termination. For next employee B when I change to resignation or end of contract, the list options still show and affect the final results ! List options should disappear whenever termination is not selected.

I could have set a 3-option list (yes, no, na) and the let the user decide the right answer. But, I wanted to eliminate a chance for human error here.

I appreciate your efforts and thank you again Mr. Bosinander for your help.

bosinander's avatar
bosinander
Iron Contributor
Dec 14, 2021

Hi Ghostgirl 

You're welcome 🙂

 

> when I change to resignation or end of contract, the list options still show and affect the final results

Could it be that you have manual calculation chosen (File: Options: Formulas? If so, pressing F9 will recalculate and show the correct list.

 

The attached file has "Not Applicable" instead of empty and also shows the list options in d9.

 

Since your formulas use AND(C7="Termination";C9="No") there ought not to be an erroneous result since C9 affects the result only if C7 is "Termination".

However, it is a very good approach to minimize human mistakes by design 🙂

Since the calculation already seems to take care of the logic, maybe B9 simply could be

If Termination, Does Article 80 apply?

and thus not need to change the options (if it still does not work out as wanted).

If you attach a file that does not work out (person B), it may be a help to fix the remaining issue.

  • Ghostgirl's avatar
    Ghostgirl
    Copper Contributor
    Dec 19, 2021

    Dear Bosinander,

    I appreciate your continuous support, I checked File, Formulas, and found calculations are set to automatic (they are not manual).

    Cell B9 is actually set as you have suggested: If Termination, Does Article 80 apply?

    The issue is with C9, which contain yes no list should appear ONLY when "Termination" (in C7) is selected.

    Taking a record of how this happens will take me some times, but if you try it from your side, you will see the yes-no list (in C9) appear when when you change from termination to end of contract or resignation.

    Actually I am trying to change the whole design so this does not happen.

     

    Thank you very much.

    • bosinander's avatar
      bosinander
      Iron Contributor
      Dec 20, 2021

      Ghostgirl, Hi

      My intention was to change the content of the list since having or not having the list cannot be done without macros.

       

      Using macros though, it is possible to fully get rid of the list.

       

      A trigger on the sheet object calls module procedures that set or delete the the validation list.

      Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$7" Then
        If Range("c7").Value = "Termination" Then
            validationOn
        Else
            validationOff
        End If
    End If
End Sub

      Two procedures manages setting or deleting the list.

      Sub validationOn()
    With Range("C9").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Yes,No"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
    
Sub validationOff()
    Range("C9").ClearContents   'clear any previous choice
    
    With Range("C9").Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

       For more details, see a search like 

      https://duckduckgo.com/?q=excel+macro+trigger&t=brave&ia=web

      A file with that solution is attached and since it contains macros, it ends with xlsM instead of xlsX. If you don't want to download it, the macros are withheld above.

Resources