Reserved dates

[darkblue]

Sheet 1 Columns:

A: Customer name
B: Arrival date
C: Departure Date
D: Room No

Sheet 2 Columns:

A: Dates (01.01.2010, 02.01.2010, etc)
B: Room 1
C: Room 2
D: Room 3

How can i color for instance B column on sheet 2 according to
reservation dates on sheet1 data ?
Any idea ? Thanks.

 

[Bob Phillips]

Create range names or the arrival dates, departure dates and room numbers on
sheet1 and then use a CF formula of

=ISNUMBER(MATCH(1,(Rooms=B$1)*(ArrivalDates<=$A2)*(DepartureDates>$A2),0))

HTH

Bob

"darkblue" wrote in message

Sheet 1 Columns:

A: Customer name
B: Arrival date
C: Departure Date
D: Room No

Sheet 2 Columns:

A: Dates (01.01.2010, 02.01.2010, etc)
B: Room 1
C: Room 2
D: Room 3

How can i color for instance B column on sheet 2 according to
reservation dates on sheet1 data ?
Any idea ? Thanks.

 

[alanglloyd]

Sheet 1 Columns:

A: Customer name
B: Arrival date
C: Departure Date
D: Room No

Sheet 2 Columns:

A: Dates (01.01.2010, 02.01.2010, etc)
B: Room 1
C: Room 2
D: Room 3

How can i color for instance B column on sheet 2 according to
reservation dates on sheet1 data ?

Quick (and maybe dirty<g>).

Put column(s) in Sheet2 which copy the dates in Sheet1. Then Format |
Conditional Format the Sheet2!ColumnA in accordance with the values in
the new column(s) in Sheet2. Then hide the new columns in Sheet2.

Alan Lloyd

 

[darkblue]

Create range names or the arrival dates, departure dates and room numberson
sheet1 and then use a CF formula of

=ISNUMBER(MATCH(1,(Rooms=B$1)*(ArrivalDates<=$A2)*(DepartureDates>$A2),0))

HTH

Bob

"darkblue"  wrote in message


Sheet 1 Columns:

A: Customer name
B: Arrival date
C: Departure Date
D: Room No

Sheet 2 Columns:

A: Dates (01.01.2010, 02.01.2010, etc)
B: Room 1
C: Room 2
D: Room 3

How can i color for instance B column on sheet 2 according to
reservation dates on sheet1 data ?
Any idea ? Thanks.

Thank you Bob, it works charmingly.
Rgds