URGENT: Data Validation to prevent entry of multiple dates in the same row

[lucy61176]

I used the following formula for data validation of 2 cells in the same row but now I need to know how to write the formula for 3 cells. My goal is to prevent the user from entering more than one date in the same row.

Data Validation for C11
=G11=""

Data Validation for G11
=C11=""

I need to get this worksheet working by the end of the day, so any help you can provide is GREATLY appreciated! Thank you

 

[DILIPandey]

Hi luck,

You can use below formula in custom category under data validation:-
=COUNTIF(11:11,G11)<4

You can also copy across this formula in entire row 11 and this validation will not allow a same date to be entered in row 11 more than 3 times.


Regards,
DILIPandey

 

[lucy61176]

Thank you, but I am not looking for duplication of the same date. What I want is if the user enters a date in A11, they get an alert if they try to enter a date in either E11 or G11. Or they enter a date in E11 and get an alert if they try to enter a date in A11 or G11. Or they enter a date in G11 and get an alert if they try to enter a date in A11 or E11. Doesn't matter what dates they are entering, just the fact that they are trying to enter a date in more than one cell in that row.

Hi luck,

You can use below formula in custom category under data validation:-
=COUNTIF(11:11,G11)<4

You can also copy across this formula in entire row 11 and this validation will not allow a same date to be entered in row 11 more than 3 times.


Regards,
DILIPandey

 

[lucy61176]

If anybody can help me, all I want to do is combine my two formulas above into one data validation.

Data Validation for C11
=G11="" and =A11=""

Data Validation for G11
=C11="" and =A11=""

Data Validation for A11
=C11="" and =G11=""

 

[DILIPandey]

HI lucky,

Use below validation formula for A11:-
=COUNTA(E11,G11)=0

Similarly, you can use this for other two cells after changing the references. thanks.


Regards,
DILIPandey

 

[Peter_SSs]

If the cells in question are A11, C11 and G11 and your requirement is that only one of them should have a value entered then you could have the same Custom Data Validation in all 3 cells, without changing any references.

=COUNTA($A11,$C11,$G11)=1


Edit: Also, in relation to the urgency of your request you might note the bold part of the final sentence in point 12 of the Posting Guidelines

 

[DILIPandey]

I agree with Peter.


Regards,
DILIPandey

 

[lucy61176]

Thanks, Peter. That one is a bit easier to understand versus the COUNTBLANK, although it did work for me. I will keep your comments in mind the next time I have an urgent question. Thanks to everyone for helping!

 

[Peter_SSs]

Thanks, Peter. That one is a bit easier to understand versus the COUNTBLANK, although it did work for me. I will keep your comments in mind the next time I have an urgent question. Thanks to everyone for helping!

Glad you have it sorted.