Thanks:  0
Likes:  0

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

Thanks

2. Data validation using the COUNTIF worksheet function.

3. 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

4. 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.

5. 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.

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•