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.
Step 2
Select Custom from the Drop Down in the Data Validation box.
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.
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.
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.
Step 7
Now to copy Data Validation to other cells select Validation in the paste special window.
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.
