Home/ Questions/Q 1013
Next
Answered
computing
  • 5
Asked: 2021-10-20T10:24:28+00:00 In:

Solved Use If Function in Data Validation (Excel)

  • 5

I wish to use the following function in value field of Data validation
IF(A8=””,IF(A7=””,A6+1,A7+1),A8+1)

I wish to allow only just next number entry in the cell with regard to entry made in previous cells whichever has value first.

Share

    1 Answer

    1. Best Answer
      If you do need to have the user enter a value in a cell, I think that this Data Validation workaround will work.

      I don’t believe that you can use a Nested IF in Data Validation since the first FALSE it encounters will trigger the Data Validation message, even if the overall IF statement eventually resolves to TRUE. So we need to use an simple IF function in the Data Validation wizard, one that only has a single logical_test.

      This workaround will require a Helper Cell, e.g. A1

      Let’s say the user-entry cell that you want to validate is A9. Put this formula in A1:

      =IF(AND(A8<>"",A9=A8+1),1,
 IF(AND(A8="",A7<>"",A9=A7+1),1,
 IF(AND(A8="",A7="",A9=A6+1),1,0)))

      This will put a 1 or 0 in A1 if the conditions you asked for are met.

      Now, select Data Validation…Custom for A9 and use this formula:

      =A1=1

      When the Nested IF in A1 resolves to 1, the Data Validation will be TRUE and the value will be allowed.

      Let us know if that works for you.

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      • 0