I want to apply data validation (based on list) and formula to calculate value in a cell.
Problem: I have to make a excel template which includes Task, Task_Priority(values:1,2,3,4,5) and Task_Complexity(values: S,M,C,NA).
Rules: (All the activities below, happen only for Task_Complexity column).
- Task_Complexity can only have following 4 values: S, M, C, NA. So,I want to apply data validation on Task_Complexity column based on list. (please suggest for any other method).
- If Task_Priority is 3,4 or 5; the Task_Complexity should automatically be updated to "NA".
- If Task_Priority is 1 or 2; the user have to select S,M or C for Task_Complexity manually.
Please help me on this in writing formulas and validation for same cell. Thanks