Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

The prohibition entering data into Excel cells provided in other cells

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 help0 please see tutorial Data Validation on this blog.

Figure 1.

How to prohibit or prevent the entry or enrollment data in Excel cell if not all conditions

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 cells D2:G2 filled Excel will then allow the enrollment date of account closing (completion accounts).
SUM 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 range "D2:G2" 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).

Figure 2.
Data Validation formula

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.

Figure 3.
How to set up a warning message in Excel Data Validation

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)

Figure 4.
The warning message "Error Alert" about preventing data entry because it did not fulfill the requirements

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 cell A1 in which write data.

We require in cell B1 do not allow enrollment of any indication if there is already a fact in cell A1.

So if cell 'A1' contains some data ban entry in cell B1
  1. Select the cell B1
  2. Start the Data Validation
  3. In the zone "Allow" Set "Custom"
  4. 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
Figure 5.
How to prohibit the enrollment or data entry in Excel cell when the condition from other 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:
  1. Select all cells in which limits the maximum value or the maximum number
  2. Start the Data Validation clicking the Data => Data Validation
  3. In the "Allow" Set "Whole number" [if it comes to decimal numbers, select "Decimal"]
  4. In the "Data" set "less than" (other options: less then or equal to)
  5. 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)
Figure 6.
How to to limit the maximum number of enrollment in Excel cell using Data Validation

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'.

Figure 7.
How to use data Validation to prevent the entry number that contains more than one digit