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

[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 four holiday cottages. The table has two years worth of dates, as we often get bookings up to two years in advance. Squares are green for available, red for booked. A table like this makes it much more convenient for potential customers to see what is available.

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

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

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

many thanks

Spike

[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

[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. I have been trying to go through it to see if I can understand how it all works.

You have been so helpful, and obviously put so much time to this, I am almost afraid to ask, but.... suppose I wanted to put the price for the 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

Spike

[DOR]

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

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

DOR

[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

[SpikeUK]

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