Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SpikeUK
 
Posts: n/a
Default Move merged cells with conditional formatting?


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 |
+-------------------------------------------------------------------+

--
SpikeUK
------------------------------------------------------------------------
SpikeUK's Profile: http://www.excelforum.com/member.php...o&userid=28997
View this thread: http://www.excelforum.com/showthread...hreadid=487999

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOR
 
Posts: n/a
Default Move merged cells with conditional formatting?

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SpikeUK
 
Posts: n/a
Default Move merged cells with conditional formatting?


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


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

--
SpikeUK
------------------------------------------------------------------------
SpikeUK's Profile: http://www.excelforum.com/member.php...o&userid=28997
View this thread: http://www.excelforum.com/showthread...hreadid=487999

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOR
 
Posts: n/a
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOR
 
Posts: n/a
Default Move merged cells with conditional formatting?

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SpikeUK
 
Posts: n/a
Default Move merged cells with conditional formatting?


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


--
SpikeUK
------------------------------------------------------------------------
SpikeUK's Profile: http://www.excelforum.com/member.php...o&userid=28997
View this thread: http://www.excelforum.com/showthread...hreadid=487999

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOR
 
Posts: n/a
Default Move merged cells with conditional formatting?


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
------------------------------------------------------------------------
DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=487999

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOR
 
Posts: n/a
Default Move merged cells with conditional formatting?


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


--
DOR
------------------------------------------------------------------------
DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=487999

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SpikeUK
 
Posts: n/a
Default Move merged cells with conditional formatting?


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


--
SpikeUK
------------------------------------------------------------------------
SpikeUK's Profile: http://www.excelforum.com/member.php...o&userid=28997
View this thread: http://www.excelforum.com/showthread...hreadid=487999

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOR
 
Posts: n/a
Default Move merged cells with conditional formatting?


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
------------------------------------------------------------------------
DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=487999



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DOR
 
Posts: n/a
Default Move merged cells with conditional formatting?


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 |
+-------------------------------------------------------------------+

--
DOR
------------------------------------------------------------------------
DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=487999

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SpikeUK
 
Posts: n/a
Default Move merged cells with conditional formatting?


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


--
SpikeUK
------------------------------------------------------------------------
SpikeUK's Profile: http://www.excelforum.com/member.php...o&userid=28997
View this thread: http://www.excelforum.com/showthread...hreadid=487999

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formatting...cont. from 9/25 Guenzak Excel Discussion (Misc queries) 4 September 26th 05 10:55 PM
how do I maintain my conditional formatting when filtering cells? Aja Excel Worksheet Functions 0 September 21st 05 09:56 PM
Conditional formatting blank cells kalz Excel Discussion (Misc queries) 2 August 12th 05 03:57 AM
Determine cells that drive conditional formatting? Nicolle K. Excel Discussion (Misc queries) 2 January 7th 05 02:08 AM
Print cells that meet conditional formatting criteria Roundy Excel Discussion (Misc queries) 1 December 14th 04 01:13 AM


All times are GMT +1. The time now is 08:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"