Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Prevent Duplicating Times

    Hi
    Can anybody help with this please
    If two rows in the date column have the same date, is there a way to prevent the same times being used
    i.e. if the date is the 3rd of January 2012, you cannot book the same hours i.e. 10:00 to 11:00 as in another row.

    Thanks in Advance

    I am Using Excel 2010
    Attached Files Attached Files
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Since you already have DV in use, that's not an option, so I would use an additional column with a formula like this (for the Duston Community centre section):
    =IF(OR(C4="",E4="",AND(F4="",G4="")),"",IF(SUMPROD UCT(($C$4:$C$34=C4)*($E$4:$E$34=E4)*(((($F$4:$F$34 <=F4)*($G$4:$G$34>=F4))+(($G$4:$G$34>=G4)*($F$4:$F $34<=G4)))>0))>1,"Clash","OK"))
    and then use conditional formatting to highlight the clashing rows in Red based on the result of the formula.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Rory

    Thanks for the reply, would it be any simpler if I removed Data Validation?

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    No simpler really - you'll need a similar formula in the DV to validate the entries. I'd stick with a separate column and highlight conflicts myself, or redo the whole thing using an entry form rather than a worksheet.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Rory

    Thanks again, is there somewhere I can learn about entry forms?

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Search for Userforms - you would need to design and code the whole thing though.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Rory

    Thanks for that.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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