Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Duplicate dates

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    North Carolina
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Thanks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Data validation using the COUNTIF worksheet function.

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    North Carolina
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    Apr 2002
    Location
    North Carolina
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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