Move merged cells with conditional formatting?
How about creating another simple five column table on the same or a
different sheet with fixed dates (hard coded, not relative to Today, as
you have in the calendar matrix) in column A and the names of the
people who have booked each cottage in each of the days they have
booked, in columns B through E. This is probably something that is
needed to manage reservations anyway. Then in the availability
calendar use conditional formatting to colour each cell based on the
corresponding cottage-date being blank/zero or containing text. If you
do not use gridlines, this should provide almost the exact effect you
need. A week will appear as a block and a part week as a group of
individual cells.
The CF formula for cottage 1cells could be like
=INDEX(ResCottage1Col,MATCH(B2,ResDateCol))<0, and similarly for the
other cottages,
where the date is in B2 in the calendar, and the res... columns are in
the Reservations table. I used <0 because INDEX returs zero if the
cell is blank.
Colour red background if true. Colour green normally.
The only extra work this should cause is the need to copy a renter's
name through all dates, which should not be a big effort.
Would this meet the need?
Declan O'R
|