You would have seen this in various online forms, where you must fill a cell before moving to the other. This makes sure that you’re restricted unless you make certain data entry (or fill a certain field).
For example, you may not be able to choose the state unless you have selected the country first.
This construct ensures that you do not miss out on filling some of the data. Something as shown below
A colleague wanted a similar construct in Excel, and the only thing that came to my mind was Data Validation.
How to Restrict Data Entry in a Cell in based on Another Cell
In this tutorial, I will show you how to restrict entries in a cell based on a formula.
Here is how you can create this in Excel
- Select cell A2.
- Go To Data –> Data Tools –> Data Validation.
- In the Settings tab go to the Allow drop down and select Custom.
- In the Formula field, type =NOT(ISBLANK($A$1)).
- Ensure that the Ignore blank is Unchecked.
- Click Ok.
Now when you enter something in cell A2, and if A1 is empty, an error will be displayed.
You May Also Like the Following Excel Tips and Tutorials:
7 thoughts on “Enable Data Entry in a Cell in Excel only if a Dependent Cell is Filled”
Thank You!!
I want to enter a date in a cell and for excel to delete a date in another cell automatically – date entered in H6 will delete date entered previously in E6
I want to enter/restrict data entry where the cell has drop down list
I have both quantity cell and expenditure cell.
If there is no any value in my quentity cell, i want to blank in expenditure cell also. But if there is any amount in the quentity cell then I can put the value in the expenditure cell.
How can I solve this problem please help me to out.
Perhaps consider replacing =not(isblank($A$1)) with =len($a$1)>0
Firstly it uses one function instead of 2
Second it avoids certain anomalies using isblank.
Isblank is also ambiguous… =ISBLANK(” “) → FALSE, so blanks are not blanks as far as IsBlank is concerned.
IsBlank should be IsEmpty
Further confusion:-
Suppose A1 contains a formula resulting in “”.then =ISBLANK(A1) → FALSE
Suppose A! contains the text prefix ‘ and nothing else, =ISBLANK(A1) → FALSE
So all in all testing LEN 0 is preferable.
REgards
BRian
Hi Brian.. Thanks for commenting.. You are right, LEN() would be a more fool-proof way for doing this 🙂
This is simple enough, however, if you then want to apply data validation on what can be entered in A2 you can’t since only one validation is allowed for a cell. So how can you both prevent entry unless A1 is populated AND apply say a validation of a drop list in A2?