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

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.

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

  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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

7 thoughts on “Enable Data Entry in a Cell in Excel only if a Dependent Cell is Filled”

  1. 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

    Reply
  2. 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.

    Reply
  3. 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

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

        Reply

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster