Apply data validation and formula in a cell in excel

Question

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).

1. 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).
2. If Task_Priority is 3,4 or 5; the Task_Complexity should automatically be updated to "NA".
3. 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

Solution

Beware, I'm not sure I understand your question!

If the Task Priority is in B2, put =IF(B2>2,"NA","") in your cell and for validation choose a range containing only S, M and C (in separate cells).

Edit to add example:

Comments

[Ankit]

if Task Priority is in column B, Task Complexity in column C; I used formula =if(b2>2,"NA", ""). But writing this formula in cells removes the data validation of cells. For Task Priorities 1 & 2, the Complexity should be displayed as S,M,C,NA. Using the above formula removes this validation.

[Ankit]

if Task Priority is in column B, Task Complexity in column C; I used formula =if(b2>2,"NA", ""). But writing this formula in cells removes the data validation of cells. For Task Priorities 1 & 2, the Complexity should be displayed as S,M,C,NA. Using the above formula removes this validation.

[Ankit]

Don't know if I explained the question properly. I want to keep both the formula and data validation in the same cell. But it seems if I enter formula, the data validation is removed for task_priority 1 & 2. I have MS Excel 2007 in my laptop.

[Ankit]

in case if Priority is 1 or 2, i want the cell to display a dropdown list to select S,M,C. If i use this formula, the cell value is replaced by "" for priority 1 & 2.

[Ankit]

thanks, this solves the problem. First the formula is to be applied and then the data validation.