Drop down list inside if statement

Copper Contributor

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 to show drop down list (Yes,No) ONLY when termination is selected. Is this possible using if statement? or any other way?

 

Thank you.

 

 

10 Replies

@Ghostgirl Hi,

You can do it using a static array eg in G3;

={"Yes","No"}

Then have the data validation list set to

=IF(C7="termination",G3#,"")

You will have the choices as wanted and the arrow but no list when not termination.

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.

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.

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.

@Ghostgirl, Hi

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

bosinander_0-1639981501681.png

 

bosinander_1-1639981532282.png

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

bosinander_2-1639985866524.png

 

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

bosinander_1-1639985487475.png

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.

@Ghostgirl 

 

as a variant

Hi @Yea_So 

It looks to be similar to Dec 08 2021 09:19 PM but coded to use two cells on another sheet instead of an array. Still shows an empty validation list when not 'termination', but it is a variant.

=IF(Sheet1!C7="Termination";Sheet2!$A$1:$A$2;"")

 

@Yea_So Compared to 

=IF(C7="termination";G3#;I3#)

your variant

=IF(Sheet1!C7="Termination";Sheet2!$A$1:$A$2;"")

works with older versions of Excel not supporting spill areas (#) and also moves the list items to a separate configuration sheet. I think it is a good setup, using a separate sheet for setup variables.

@bosinander 

 

it's not based on two cells but just based on an if statement within the data validation.  the other cell was just a test to make sure it works within the data validation and left it there for illustration purposes. I tend to lean towards a simplistic solution for the op to expedite their project.

 

VBA is a powerful solution for those who like to explore different types of solutions but for the ones asking for a formula based solution the maintainability is better since the current user might get promoted to another opportunity or move to a different company for a career change and someone else might inherit the spreadsheet who is not familiar or inclined to use VBA and has no idea how to maintain the solution or troubleshoot it in the future.

> I tend to lean towards a simplistic solution for the op to expedite their project.
[x] Like