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:

  • Denise Prince says:

    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

  • Winnie says:

    I want to enter/restrict data entry where the cell has drop down list

  • Kapil Deo Paswan says:

    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.

  • 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.
    REgards
    BRian

    • 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?

  • >