Prevent Duplicating Times

Topic

New Reply

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

Reply | Quote

Replies

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(SUMPRODUCT(($C$4:$C$34=C4)*($E$4:$E$34=E4)*(((($F$4:$F$34=F4))+(($G$4:$G$34>=G4)*($F$4:$F$340))>1,”Clash”,”OK”))
and then use conditional formatting to highlight the clashing rows in Red based on the result of the formula.

Reply | Quote

Hi Rory

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

Regards

Braddy

Reply | Quote

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.

Reply | Quote

Hi Rory

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

Regards

Braddy

Reply | Quote

Search for Userforms – you would need to design and code the whole thing though.

Reply | Quote

Hi Rory

Thanks for that.

Regards

Braddy

Reply | Quote