Reserved dates

  • Thread starter Thread starter darkblue
  • Start date Start date
D

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.
 
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.
 
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 ?
Click to expand...

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
 
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.
Click to expand...

Thank you Bob, it works charmingly.
Rgds
 
Back
Top