0

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

|improve this question
  • 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 Jun 13 '13 at 12:13
0

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:

SU607113 example

|improve this answer
  • 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 Jun 13 '13 at 12:16
  • 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 Jun 13 '13 at 12:36
  • 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 Jun 13 '13 at 12:52
  • thanks, this solves the problem. First the formula is to be applied and then the data validation. – Ankit Jun 13 '13 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.