Excel – Apply data validation and formula in a cell in excel

microsoft excelmicrosoft-excel-2007worksheet-function

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

Best Answer

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

Related Solutions

Excel: Data Validation to make a cell required

The simplest method would be to just use Conditional Formatting. Especially if you can use tables:

If you're using a table you can simply select the column and set the conditional format to formula

=[column]2=""

Then format to fill in red.

-The upside of this is that table ranges are dynamic, so as new rows are added the formula will be retained.
-The downside of this is that there's nothing to stop them ignoring the warning.

You could use VBA, but many users will deny macros in their workbooks, disabling the function.

EDIT
A simple way to have conditional formatting show up if a cell is blank in a row that is being used, where you can't guarantee any particular cell will be populated:

=AND($[CurrentColumn]2="",COUNTA($[FirstColumn]2:$[LastColumn]2)<>0)

This formula will activate the conditional formatting if the cell you want highlighted is blank and any other cell in the row is not blank (for those who don't know, a COUNTA formula counts all cells in a range that are not empty). Be sure to exclude any cells containing formulas from the COUNTA as they will be counted as not blank.

Excel – Can the data validation list width be controlled in Excel 2007 and greater

You're right, this can occur when you change the width after creating the dropdown, but also sometimes when there are merged cells present. VBA is the only option. Place this in the worksheet object and change the column number to the column your list is in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
   If Target.Column = 4 Then
       Target.Columns.ColumnWidth = 20
   End If
End Sub

It will look for changes in your list and adjust to the specified width.

Best Answer

Related Solutions

Excel: Data Validation to make a cell required

Excel – Can the data validation list width be controlled in Excel 2007 and greater

Related Question