Data Validation Examples in Excel
The Prohibition Entering Data into Excel Cells Provided in Other Cells
In a situation where you need to prohibit or prevent the entry or enrollment data in Excel cell if not all conditions in other cells to which it is attached calculation in the application then leverage the power of Data Validation Functions in Excel.
For more help please see Data Validation tutorial on this blog.
To take an example from the image above. In the column 'I' you need to enter the date when you complete the payment account that is issued by a specific company, the respective company pays the invoice issued installments - four installments. Conditions the cells in columns D2:G3. If one of the cells is not filled data then Excel should be prohibited to enter a date in column 'I'. If D2:G2 range of cells filled Excel will then allow the enrollment date of account closing (completion accounts).
SUM Function is controlled using Conditional Formatting so that Excel cell with paint background green if the correct sum paid installments. To prevent enrollment dates in the Data Validation set the following conditions.
So, select the cells in column 'I' in which the listing date.
On the 'Settings' tab in the 'Validation Criteria' in the 'Allow' choose from the drop-down command 'Custom'
In the 'Formula', type the following formula: =COUNTBLANK(D2:G2)=0
This formula examines and counts the blank cells in the "D2:G2" range and if one of them is not fulfilled it will return a result greater than zero (0). In this way the condition is not fulfilled and therefore Excel will not allow enrollment date (data).
If you want you can set a warning message (see image below) to the respective Workbook and alert you to a problem. Of course, here is a simple example but it shows that not all cells filled. But if you have a situation of more than 100 rows and more dozens of columns then this is a good solution.
In case the condition is not fulfilled Excel will display a warning message that says you are not allowed to enter the date, or because data are not met the conditions. (picture below)
How to prohibit the enrollment or data entry in Excel cell when the condition from other cells
If you have a need to prohibit enrollment into a cell when the condition in another cell, use Data Validation. Take for example the picture to the right where we have a 'A1' cell in which write data.
We require in 'B1' cell do not allow enrollment of any indication if there is already a fact in 'A1' cell.
So if cell 'A1' contains some data ban entry in 'B1' cell.
- Select the B1 cell
- Start the Data Validation
- In the zone "Allow" Set "Custom"
- In the zone "Formula" set the formula =IF(A1="";"";"")
- On the Input Message tab, type the alert message ie The input message as instructions for filling cells
Limiting the maximum number of entries or the maximum value in an Excel cell
If you need an Excel cell to limit the maximum value of the given number in a cell then do the following:
- Select all cells in which limits the maximum value or the maximum number
- Start the Data Validation clicking the Data => Data Validation
- In the "Allow" Set "Whole number" [if it comes to decimal numbers, select "Decimal"]
- In the "Data" set "less than" (other options: less then or equal to)
- In the "Maximum" set the number one higher than your max. number (for example, in the picture to the right maximum value permitted enrollment is 99, so we set the number 100)
How to use Data Validation to prevent the entry number that contains more than one digit
If you want to excel in specific cells to allow enrollment only one digit or prohibit the entry of more than one digit or number then use Data Validation. Set minimum and maximum digits as a condition for the 'Info' and select 'between'.