Forum Discussion
Drop down list inside if statement
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.
