Quarterly Updating

[Gerry Cornell]

The quarterly spreadsheet has 13 week headers interspersed with detail
for that week thus

COLUMN A

WEEK ENDED 5 JANUARY 2007
Text Detail
Text Detail
Text Detail
Text Detail
Text Detail
Text Detail
WEEK ENDED 12 JANUARY 2007
Text Detail
Text Detail
Text Detail
Text Detail
Text Detail
Text Detail
WEEK ENDED 19 JANUARY 2007
Text Detail
Text Detail
Text Detail
Text Detail
Text Detail
Text Detail

What I would like to be able to do is to change "WEEK ENDED 5 JANUARY
2007" and for the succeeding weeks to be automatically updated. How
might this be easily achieved?

TIA



--

~~~~

Gerry

~~~~~~~~
Enquire, plan and execute.
Stourport, England
~~~~~~~~~~~~~~~~~

 

[David Biddulph]

Replace each of your "WEEK ENDED ...." cells by
="WEEK ENDED "&UPPER(TEXT(B$1+ROW()-1,"d MMMM yyyy"))
then put your starting date in B1.

 

[Gerry Cornell]

David

That's clever stuff! However, the number of rows (which vary from week
to week) between week headers cause an incorrect result.

Initially I did not test further foreseeing a second problem. The
week ends in each month are on a Friday. If the last day of the month
falls on a Saturday or Sunday I have previously taken this date
instead of the last Friday. Also if the last day of the month falls on
a Monday, Tuesday, Wednesday or Thursday I take that day as the end of
the week.

Changing the formula for the first week
="WEEK ENDED "&UPPER(TEXT($D7+ROW()-1,"d MMMM yyyy"))
for the second and subsequent weeks to
="WEEK ENDED "&UPPER(TEXT($D27+ROW()-1-(ROW(A27)-ROW($A$7)),"d MMMM
yyyy"))
overcomes the first problem. In the finished spreadsheet I would make
the column width of column D 1, effectively hiding it.

It is simple to get cell $D27 to increment by the right number of days
for weeks 2, 3 and 4. Weeks 1 and 5 are where I have so far only
managed to bodge the result.


--

Regards.

Gerry
~~~~
FCA
Stourport, England
Enquire, plan and execute
~~~~~~~~~~~~~~~~~~~