Restrict Data Entry In A Cell If Previous Cell Is Not Filled In Excel

In this post we will see how we could restrict data entry in a cell if the previous or preceding cell is not filled.

By restricting user to fill the first cell first and then second cell and so on we could make sure that there are not any gaps in the data which helps us in further processing of that data whether by code or otherwise.



We will see an example of what we mean when we say that data entry is not allowed in a cell until the previous cell is filled.

In the below pic, a user has to fill data in column A linearly, means first A1 should be filled and then A2 and then A3 and so on, to compel user to fill data in a cell only if the previous cell is filled we will use Excel Data Validation method.

And below is the pic that is showing the situation that we do not want i.e. user has filled the data irregularly and there is no provision of filling data only if the previous cell is filled.





To restrict user to fill the cell only if the previous cell is filled, we will see step by step instruction to achieve that.

Step 1

Select cell A2 and go to Data Validation under Data tab in excel as shown below.

data validation to stop user filling data in a cell if previous cell is not filled

Step 2

Select Custom from the Drop Down in the Data Validation box.

formula to stop user filling data in a cell if previous cell is not filled

Step 3




Enter formula =LEN(A1)<>0 in the formula box of Data Validation window and uncheck “Ignore blank” as shown in the pic below.

formula to restrict user filling data in a cell if previous cell is not filled

Here we have used length formula to determine the length of the value which is there in the cell A1 and our formula will only return true when any value will be available in cell A1 i.e. when it will be filled.

So It will allow user to fill data in cell A2 only if cell A1 will be filled otherwise it will give an error message

Step 5

Now we want to notify the user to fill the previous cell first, only then excel will allow user to enter value in next cells.

To do that, go to Error Alert tab in the Data Validation window and enter an error message notifying user to fill the previous cell first and then the next cell as shown below.




error message to stop user filling data in a cell if previous cell is not filled

Now click OK to implement our data validation in cell A2 which will not allow user to fill it until A1 is filled.

Step 6

Now to copy this data validation to all other cells in column A, copy cell A2 and paste special Validation to all the range that we do not want user to fill until the previous cell is filled with value.

Copy cell A2 and select range from A2 to A10 and go to Paste Special as shown in the pic below.

format to stop user filling data in a cell if previous cell is not filled

Step 7

Now to copy Data Validation to other cells select Validation in the paste special window.



validation to stop user filling data in a cell if previous cell is not filled

Now click OK and you will see the Data Validation of not allowing data entry to a cell until previous cell is filled has into effect.

Below is the error message that you would get if you tried to enter value into a cell if the previous cell is not filled.





Hope this helped.

 

Share The Knowledge

Random Posts

  • Sum Of Digits In A Cell In Excel

    We will see in this post one of the easiest way to sum up all the digits in a number […]

    Share The Knowledge
  • Change Name Range In Excel

    Name range is of the most useful features in excel.The challenge in our day to day excel activities are that […]

    Share The Knowledge
  • Generate Sample Data In Excel

    Let’s understand how we could generate some sample data to carry out some further analysis in excel. We have certain […]

    Share The Knowledge
  • Get/Extract Every Nth Value In Excel

    In this post we will see how to extract every nth value/row in excel. We will try to understand how […]

    Share The Knowledge

Leave a Reply