Duplicate dates

[Nathan B]

What's the best way to keep a user from entering the same date twice in a selected group of cells?

Thanks

[Mark W.]

Data validation using the COUNTIF worksheet function.

[Nathan B]

I've tried using this formula but I must not understand it completely. Can you give me a little more detail? Here's my exact situation. A user can enter dates in cells A5 through A19, C5 through C19, E5 through E19, G5 through G19, I5 through I19, K5 through k19, and M5 through M19. I don't want them to enter the same date twice in any of these cells. Please help me figure this out.

Thanks

[Mark W.]

On 2002-04-21 17:40, Nathan B wrote:
I've tried using this formula but I must not understand it completely. Can you give me a little more detail? Here's my exact situation. A user can enter dates in cells A5 through A19, C5 through C19, E5 through E19, G5 through G19, I5 through I19, K5 through k19, and M5 through M19. I don't want them to enter the same date twice in any of these cells. Please help me figure this out.

Thanks

Setup Data Validation for A5 using...

=COUNTIF($A$5:$A$19,A5)+COUNTIF($C$5:$C$19,A5)+COUNTIF($E$5:$E$19,A5)+COUNTIF($G$5:$G$19,A5)+COUNTIF($I$5:$I$19,A5)+COUN TIF($K$5:$K$19,A5)+COUNTIF($M$5:$M$19,A5)<2

...and, apply to all cells in the ranges listed above.

[Aladin Akyurek]

On 2002-04-21 17:40, Nathan B wrote:
I've tried using this formula but I must not understand it completely. Can you give me a little more detail? Here's my exact situation. A user can enter dates in cells A5 through A19, C5 through C19, E5 through E19, G5 through G19, I5 through I19, K5 through k19, and M5 through M19. I don't want them to enter the same date twice in any of these cells. Please help me figure this out.

Thanks

Set up custom data validation for
A5 with the formula:

=SUMPRODUCT(($A$5:$M$19=$A5)*(MOD(COLUMN($A$5:$M$19),2)<>0))<2

and copy the cell to A15;

for C5 by changing $A5 to $C5:

=SUMPRODUCT(($A$5:$M$19=$C5)*(MOD(COLUMN($A$5:$M$19),2)<>0))<2

and copy the cell to C15.

Repeat the procedure for the rest of the ranges of interest.

Aladin

[Nathan B]

I tried Mark W's formula and it works great. I didn't try the other formula. Thanks to everyone for the help.