Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|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

1 Answer 1

up vote 0 down vote accepted

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

share|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
    
What Excel version? –  pnuts Jun 13 '13 at 12:18
    
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
    
Odd, same version and works for me. Is it that NA is selected with Circle Invalid Data? That can be addressed by adding NA into the validation range (though this would then permit users to select NA). –  pnuts Jun 13 '13 at 12:41
    
Did you clear validation BEFORE entering the formula? –  pnuts Jun 13 '13 at 12:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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