How to stop conflicts with conditional formatting

**Gooford** · 10-05-2009, 06:59 AM

Hi

I have an example of a booking spreadsheet I am putting together. The sheet colours cells according to date ranges entered (these represent bookings). Upto four separate booking dates are allowed in a month, how can amend the conditional formula to stop any duplicate dates being accepted in a particular room (double bookings)?

Current formula is and($a4<=c$2,$b4>c$2) This then amends for second, third and fourth bookings in columns AI-AJ. AL-AM, AO-AN.

Also, when I put together all the other months, is there an easy way of putting dates across the top which will save me having to manually type them in?

Thanks

**NBVC** · 10-05-2009, 07:55 AM

It's not very clear what you mean?

Can you elaborate with examples?

**Gooford** · 10-05-2009, 10:43 AM

Originally Posted by NBVC

It's not very clear what you mean?

Can you elaborate with examples?

Sorry:-

If I enter an arrival date 1 Jan 09 and a departure date 10 Jan 09 this blocks out these dates in orange on the attached sheet.

I have the option of 3 addition bookings in a month in a given room so I could then type in 9 Jan 09 until 20 Jan 09 and block that out in orange. However this would mean that the 9 Jan and 10 Jan are covered by both date ranges, therefore double booked (room one, rows 3 and 4).

I wondered if there was anything I could do to prevent the second date range being accepted if it meant that it overlapped with the first.

**NBVC** · 10-05-2009, 11:09 AM

You can use Data Validation possibly...

e.g..

While in AI4, go to Data|Validation and select Custom from Allow menu

Enter formula: =AI4>B4

this forces AI4 to be later than any date entered in Departure Date in B4...

You can customize the error message through Error Alert tab..

use similar approach in other cells.

Is that what you meant?

**Gooford** · 10-05-2009, 11:18 AM

Originally Posted by NBVC

You can use Data Validation possibly...

e.g..

While in AI4, go to Data|Validation and select Custom from Allow menu

Enter formula: =AI4>B4

this forces AI4 to be later than any date entered in Departure Date in B4...

You can customize the error message through Error Alert tab..

use similar approach in other cells.

Is that what you meant?

Spot on thanks!

**NBVC** · 10-05-2009, 11:27 AM

Just a note...

if you intend to copy data validation from one cell to the other, then make column B absolute by adding $

e.g.

=AI4>$B4

so that all validation refers to column B in respective row