Enable Data Entry in a Cell in Excel only if a Dependent Cell is Filled

You would have seen this in various online forms, where you must fill a cell before moving to the other. This construct ensures that you do not miss out on filling some of the data. Something as shown below

Dependent Cell is Filled in Excel

A colleague wanted a similar construct in Excel, and the only thing that came to my mind was Data Validation.

Here is how you can create this in Excel

  1. Select cell A2.
  2. Go To Data –> Data Tools –> Data Validation.
  3. In the Settings tab go to the Allow drop down and select Custom.
  4. In the Formula field, type =NOT(ISBLANK($A$1)).
  5. Ensure that the Ignore blank is Unchecked.
  6. Click Ok.

Dependent Cell is Filled in Excel

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:

  • Brian Canes says:

    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.

    • Sumit Bansal says:

      Hi Brian.. Thanks for commenting.. You are right, LEN() would be a more fool-proof way for doing this 🙂

      • Dan Walker says:

        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?

  • >