Data validation formula rules

[Retroshift]

Hi,


I have a formula in data validation.

=AND(MOD(A1,1)=0,MOD(C1,1)=0,A1+C1<10,MIN(A1,C1)>=0,MAX(A1,C1)=5)

This is what it does:
The values of two cells A and C must be integers that cannot be lower than 0 and not be higher than 5. You get a data validation error message if the cell entry does not comply with this rule.
But in the same data validation there is a rule that when cell A has a value of 5, cell C must have a value from 0 to 4. And this vice versa (i.e. when cell C has a value of 5, the value of cell A must be from 0 to 4).
If this is not the case, you get a data validation error message. Additional to these rules, one of both cells should always have a value of 5.


The formula poses two issues:
1) When for example cell A has a value of 4 and cell C a value of 5, but you want to change the value of cell A to 5 (and then the value of cell C to 4), you get the error message. Could it be possible to overwrite the values without the error message popping up and without having to remove the contents of both cells before entering the new values?


2) If cell C is empty and you want to enter a value in cell A, it allows you to enter a value higher than 5, which is actually against the data validation rule (max value is 5). The ignore empty cells box is checked.
In the same situation, if you uncheck the ignore empty cells box, you cannot enter a value higher than 5 but not lower than 5 either, only 5 itself. How could this issue be overcome by adapting the data validation formula so that an entry in cell A can range from MIN 0 to MAX 5 when cell C is empty?

I enclosed the file with the data validation rule.


Thanks

[mancubus]

?

=AND(0<=A1,A1<=5,0<=C1,C1<=5,A1+C1<10,OR(IF(A1<>5,C1=5),IF(A1=5,C1<>5))) 

[Retroshift]

Thanks for the reaction Mancubus, but the formula does not work when you have two empty cells A and C. In that case, you can only enter a value of 5 in cell A. Values below or above 5 trigger the error message. The same situation goes for cell C.

[mancubus]

you are welcome.

this bit is redundant, so i removed it: A1+C1<10

=AND(0<=A1,A1<=5,0<=C1,C1<=5,OR(IF(A1<>5,C1=5),IF(A1=5,C1<>5))) 

this formula returns TRUE when the following 3 conditions are met at the same time:
1) A1 contains a value between 0 and 5, inclusive.
2) C1 contains a value between 0 and 5, inclusive.
3) A1's OR C1's value must be 5 and when one's value is 5 the other's value must not be 5).

a blank cell means its value is 0, so the formula works with blanks too.

if your conditions are different than these please let me know.

[mancubus]

you can add your whole number condition as well

=AND(0<=A1,A1<=5,0<=C1,C1<=5,MOD(A1,1)=0,MOD(C1,1)=0,OR(IF(A1<>5,C1=5),IF(A1=5,C1<>5))) 

[Retroshift]

I added the Excel file. Even though merged cell G5 is blank/empty, you are unable to enter a value other than 5 in it, with the formula you mentioned. You should however be able to enter a value <=5 (but not higher than 5). Same goes for merged cell J5. It seems that the first value you enter in one of both empty cells, can only be 5. Any ideas why this is?

[mancubus]

Retro,

review the conditions i have written, and correct them where necessary.
i dont need a file at the moment.

if the conditions are OK, first clear A1 (or C1 or both) then re-enter another value.

[Retroshift]

Hi, the 3 conditions you have mentioned, including the whole numbers test, is exactly what I'm looking for.

The issue with the formula is, however, that you cannot re-enter values in the empty or cleared cells other than 5.

I kindly advise you to look at the file to see what I mean. For example, if I enter value 4 in cell G5, the error message pops up (which it shouldn't <=5).

Thanks

[Bob Phillips]

I think the IFs in your DV formula should be ANDs.

[Retroshift]

Thanks for the suggestion xld. I tried the changed formula in the attached test file, but the issue remains the same..

[Bob Phillips]

What exactly is the issue then? Describe what you input and what happens step by step.

[Retroshift]

Ok, I kindly advise you to open the file I attached.

Try entering the value 4 in empty cell G5. It won't work, because the DV error message will be triggered. However, the DV error message should not be triggered for values lower or equal to 5. You should be able to enter those values. I don't understand why this won't work.

[Bob Phillips]

Your formula says that if G5 is not 5 then J5 must be 5. IF it is, it returns TRUE with 4 in G5. If J5 is blank then it will fail because of that.

[snb]

Hou het simpel; voor G5:

=(J5>=0)*(INT(G5/6)=0)*(INT(G5)=G5)

Gebruik nooit 'merged cells'

[Retroshift]

Your formula says that if G5 is not 5 then J5 must be 5. IF it is, it returns TRUE with 4 in G5. If J5 is blank then it will fail because of that.

Yeah, I tried with the AND instead of the IFs, but the same issue keeps appearing.

=(J5>0)*(INT(G5/6)=0)*(INT(G5)=G5)

This function does not work in Data Validation; and why divide G5 by 6?

[snb]

To get the desired result.

Sub Macro4()
    Cells(5, 7).Validation.Delete
    Cells(5, 7).Validation.Add 1, 1, 1, "0", "5"
End Sub