Retroshift
05-25-2017, 12:49 PM
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
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