Categories
Excel

Data Validation Based on Another Cell

Data validation in Excel allows you to restrict the type of data that can be entered into a cell. You can use data validation to create rules based on another cell value.

One way to prevent errors in data entry is to use the Data Validation tool. This tool allows us to apply a logical condition to a cell and reject any input that does not meet the condition.

  1. Allow entering a value into a cell if another cell is not empty.
  2. Allow entering only numbers into a cell if another cell is not empty.

Allow entering a value into a cell if another cell is not empty

For example, suppose we have a cell that requires a value only if another cell has a value. We can use the Data Validation tool to check if the date cell is empty or not, and only accept the value if it is not empty:

  1. Select the cell range you want to enter the data
  2. Click Data > Data Validation.
  3. The Data Validation dialog box will be displayed.
  4. On the Settings tab, select Custom from the Allow list.
  5. Type =A2:A5<>"" (any range you want to validate) into the Formula box.
  6. Click the Error Alert tab, enter a Title and Error Message
  7. Click OK.

The =A2:A5<>"" formula checks if each cell in the range A2:A5 is empty or not. The formula uses the <> operator, which means “not equal to”. So, the formula =A2:A5<>"" means “compare each cell in A2:A5 to an empty”.

When you attempt to enter a value into a cell in the Age column without entering the value into the cell in the Name column, the validation rule is triggered, and the error alert is shown:

Allow entering only numbers into a cell if another cell is not empty

=IF(A2:A5<>””,ISNUMBER(B2:B5),FALSE)
  1. Select the cell range you want to enter the data
  2. Click Data > Data Validation.
  3. The Data Validation dialog box will be displayed.
  4. On the Settings tab, select Custom from the Allow list.
  5. Type =IF(A2:A5<>"",ISNUMBER(B2:B5),FALSE) (or any range you want to validate) into the Formula box.
  6. Click the Error Alert tab, enter a Title and Error Message
  7. Click OK.

The =IF(A2:A5<>"",ISNUMBER(B2:B5),FALSE) formula is used to check if the cells in column B are numbers only and if the corresponding cells in column A are not empty.

The IF function takes three arguments:

  1. A logical test
    The logical test is A2:A5<>"", which means that the cells in the range are not blank.
  2. a value if true
    The value if true is ISNUMBER(B2:B5), which returns TRUE or FALSE depending on whether the cells in the range are numbers or not.
  3. and a value if false.
    The value if false is FALSE, which means that if the cells in the A2:A5 are blank, the formula will return FALSE regardless of the values in B2:B5.

Now, if you enter a non-number value into a cell in the Age column without entering the value into the cell in the Name column, you’ll get an error alert:

Data Validation