Data validation function rules

[Retroshift]

Hi, I'm looking for a function within data validation:

I have cell A and cell C. The values of 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 should be 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.

Thanks!

[Phuocam]

Try:

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

[Phuocam]

See attached ...

[Retroshift]

Very nice, Phuocam. However, I forgot to mention that, additional to these rules, one of both cells should always have a value of 5.

[Phuocam]

Edit:

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

[Retroshift]

Thanks, Phuocam. This formula is correct but it gives a problem. 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 ... Can this be solved so you can change it without getting the error message?

[Phuocam]

Remove content A and C before the change

[Retroshift]

Is there another way to do this? So you can overwrite the values without the error message popping up? The process of quickly altering large data sets gets slowed down significantly with this extra step.

[Retroshift]

I have a question. 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 I 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?

[FDibbins]

Retroshift Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

https://www.mrexcel.com/forum/excel-...ula-rules.html