How to apply nested IF functions in data validation

Hi peeps.

I've been trying to create a data validation that only allows users to enter numeric values in cells of column L if a couple of conditions are met. They are:

If K1 < I1 then K1+L1<=I1, L1 is a positive number (bigger than 0), and L1 is mandatorily a number (no text allowed). If possible, I would also like to prevent users from entering numbers in L1 if K1 is blank (well, they may even try to, but Excel should warn them that no numbers can be entered in L1 if K1 is blank).

I tried many things but I only managed to get to this point:

Formula:

Please Login or Register  to view this content.

As you guys notice, it does not meet all conditions I want to validate but honestly I don't have a clue of how to make this work. I also tried to apply the "Whole numbers" validation and pasted the conditions in the maximum value. Didn't work either.

Any ideas of what I should do?

Thanks.

Hi adriano.r.marques, please see attached for my solution.

The data validation uses the following formula

Please Login or Register  to view this content.

Also, unchecking Ignore Blank is essential.

Hi quekbc. Thank you for your help. It did work!! It looks so simple that I feel stupid.

Of course L2<=$I2-$K2 was the right way of saying that. I don't know what I had in mind when I wrote that dumb formula! Oh my!

But would you mind telling me how does Excel know that no text input is permited? Is it because of the L2>0 part?

I really appreciate your help.

Originally Posted by adriano.r.marques

But would you mind telling me how does Excel know that no text input is permited? Is it because of the L2>0 part?

Glad that worked for you! The reason why texts do not quite work in this instance is due to L2<=$I2 - $K2. When comparing texts to a number, Excel will treat any text as larger than any number. (I.e. do a sort on a list of numbers and alphabets, Excel will arrange numbers first, followed by alphabets/texts). So, a text of any kind "L2" is always "larger" than any number "I2-K2"

Genius!