Move merged cells with conditional formatting?

[SpikeUK]

Hi,

I have a spreadsheet as per the enclosed attachment. The top date is
always Today's date, regardless of when the spreadsheet is opened. The
rest of the dates are Today's date plus 1, plus 2, plus3, etc.
Therefore, the dates are moving up the table, day by day. The Saturdays
are linked to the correct dates by using the
=IF(WEEKDAY(F6)=7,"Saturday", "") function, so they are moving up the
screen as well.

The horizantal lines between the weeks move correctly due to
Conditional Formatting (thanks to bpeltzer for that tip).

I would like to merge the blocks of cells that make up a week in such a
way that the merged group of cells moves up the table correctly with
their corrosponding dates.

I have manually merged the top row of cells in the attachment to
indicate what I intend to do, but tomorrow, when the dates have moved
up one, it will look all wrong again.

Is what I want to do possible, and if so, how is it done?

many thanks

Spike


+-------------------------------------------------------------------+
|Filename: dates.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4064 |
+-------------------------------------------------------------------+

 

[DOR]

Spike,

What you want to do is not possible without using VBA to unmerge and
remerge cells every day, since it is not the logical set of cells
starting with Saturday that would merged but, as in your current case,
the physical sets of cells x6 through x12, x13 through x17, etc. You
will not get a lot of support for merged cells on these forums, since
the gurus tend to dislike them, generally with good reason.

Why not just turn off your grid lines and it will look as if you have
merged cells. If you then need to center text vertically in those
areas you may be able to do so through appropriate functions.

Why are you trying to merge the cells for each week? If we knew,
someone might have an alternative solution.

HTH

Declan O'R

 

[SpikeUK]

Thanks for replying, D OR.

This is to be an availability page to be uploaded to my site, for fou
holiday cottages. The table has two years worth of dates, as we ofte
get bookings up to two years in advance. Squares are green fo
available, red for booked. A table like this makes it much mor
convenient for potential customers to see what is available.

Most of the time, the bookings are for whole weeks, from Saturday t
Saturday, therefore it would be easier if the cells for a whole wee
were merged, as per the first line in the enclosed attachment. However
the table could function without merging the cells, it would just mea
selecting a group of 7 cells for a week, to colour red, instead of jus
one.

However, out of season, we often have bookings for part of weeks, s
then it would be useful to be able to un-merge the cells for that wee
to show exactly which days are booked, as per the second and thir
lines of the enclosed attachment.

The table will be uploaded regularly so that it is always up to date.
want it to be as simple as possible to use, as I am putting thi
together for someone else, who is definately not a computer guru, t
use.

many thanks

Spik

+-------------------------------------------------------------------
|Filename: dates.jpg
|Download: http://www.excelforum.com/attachment.php?postid=4065
+-------------------------------------------------------------------

 

[DOR]

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

 

[DOR]

I've implemented my suggestion and will send it to you if you send me
an e-mail. The CF is not exactly straightforward, as you need to use
all three conditions to get the color with and without the weekly
dividing line and then the dividing line on its own. It is also
possible to implement it so that it runs like molasses in January in
Butte, Montana, but there is an easy fix to eliminate the inefficiency.

Reply direct and I will send you the spreadsheet.

Regards

Declan O'R

 

[SpikeUK]

Thanks for the trouble you have been to.

I can't seem to find a way to send you a private message. Your name DOR
doesn't seem to be a link with private message as one of the options. I
can't find you in the members list, either.

I am not sure how to get my email address to you, which is a nuisance
as I would like to see your spreadsheet.

regards

Spike

 

[DOR]

Spike,

My spreadsheet should be attached. Since I referred to possible
inefficiencies in my previous post, I should describe what they are,
for the benefit of other readers. The formula I suggested for the CF
had a MATCH in it, and it was used in two conditions, in four cells per
day, which, if reservations are shown for two years ahead, could amount
to almost 6,000 matches per recalculation, into a 700 row column.

The solution was to add a (hidden) helper column to the calendar with
one MATCH for the Today row and increment that value by one on each
successive row. This reduced the number of matches to one. This row
was then used for the INDEX into the Reservations table in the CF
formulas.

This approach does not test for absence of a date on the reservations
sheet. If that is necessary, you will need to put a MATCH for the date
in every row of the helper column, and also add a visible column beside
it with a formula such as

=IF(ISNA(helpercolumncell,"ADD THIS DATE TO THE RESERVATIONS
SHEET!!!","")

coloured red or something garish so that whever you extend the
calendar, omitted dates will jump out at you.

HTH

Declan O'R


+-------------------------------------------------------------------+
|Filename: HolidayCottageRes.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4066 |
+-------------------------------------------------------------------+

 

[DOR]

I hope you were able to download the spreadsheet. Here's another
thought - If you always want your calendar display to start on a
Saturday, the following formula could be substituted for your =TODAY()
formula:

=TODAY()-WEEKDAY(TODAY()+1)+1

This will keep your start day on the Saturday that precedes until today
is Saturday when it will start on today.

Hope this helps

Declan O'R

 

[SpikeUK]

Yes, thank-you DOR, I downloaded it last night. It works perfectly.
have been trying to go through it to see if I can understand how it al
works.

You have been so helpful, and obviously put so much time to this, I a
almost afraid to ask, but.... suppose I wanted to put the price for th
week in the middle cell of each week, for each of the four cottages
would that be possible?

I suppose it would involve a similar table to the Reservations Table
but called Prices, and transfer the data across in a similar way.

I quite understand if you think you have done enough on this already,

many thanks

Spik

 

[DOR]

That should not be difficult at all. I am away from home right now bu
will return later today and will add it to the spreadsheet. I envisio
another table with one row per week and the price for each cottage o
the row. Then a formula in the calendar to determine if it is th
right day and a look up, possibly using index/match again back to th
price table for the price. If we put it on a Tuesday it will apppea
in the middle of the week.

I just realised I can download the spreadsheet from the forum, so
will do that and repost it.

DO

 

[DOR]

Here it is. I also added the "always start on Saturday" formula, and I
left the two helper columns on the reservation sheet visible. ou can
hide them later.

You will also need to format the rates for how you want to display
them. If you want to show them on a different weekday, just change the
weekday number in the formula. Note that it will be hard to show the
rates larger in relation to the week boxes because that will change all
row heights also, thereby keeping the characters the same relative
height, and you can,t change the height of the Tuesday row alone since
it is a different row every day.

If the rate font is not large enough for the web, you may need to
display a rate tabel independently of the calendar.

After all this, I hope you can let me kno whow to find the actual web
site so that I can view the results of the effort!!

Hope this helps

Declan O'R


+-------------------------------------------------------------------+
|Filename: HolidayCottageReservations3.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4071 |
+-------------------------------------------------------------------+

 

[SpikeUK]

Sure thing, I'll post it when it's up and running.