Solved Use If function in data validation (Excel)

May 5, 2015 at 00:01:25
Specs: Windows 7
I wish to use the following function in value field of Data validation

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

See More: Use If function in data validation (Excel)

Report •

May 5, 2015 at 04:52:28
You can't use that type of formula in Data Validation. A Data Validation formula must resolve to TRUE or FALSE. See step 5 here:

If you want to restrict the values in a cell to the results of the Nested IF, why not just use the IF formula in the cell and then lock the cell so the user can't alter it? In other words, just let the formula return the desired result instead of having the user enter a value.

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

Report •

May 5, 2015 at 10:18:35
✔ 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:


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:


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.

Report •

May 7, 2015 at 23:33:23
That's perfect Derby !!
It worked !!

Thanks a ton !!

message edited by Vipul07

Report •

Related Solutions

Ask Question