Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quarterly Updating
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 ~~~~~~~~~~~~~~~~~ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quarterly Updating
Gerry,
In A1 enter 9/1/07 and use the custom format _Week ending dd/mm/yyyy_ to display the entry. In A6 enter =A1+7 with the same custom format. Hope this helps, Fred "Gerry Cornell" wrote: 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 ~~~~~~~~~~~~~~~~~ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quarterly Updating
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. -- David Biddulph "Gerry Cornell" wrote in message ... 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 ~~~~~~~~~~~~~~~~~ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quarterly Updating
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 ~~~~~~~~~~~~~~~~~~~ "David Biddulph" wrote in message ... 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. -- David Biddulph "Gerry Cornell" wrote in message ... 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 ~~~~~~~~~~~~~~~~~ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I caluclate return on investment, compounded quarterly? | Excel Worksheet Functions | |||
Excel 2003 links are not updating between workbooks | Excel Worksheet Functions | |||
Excel Not Updating | Excel Discussion (Misc queries) | |||
quarterly reports | Excel Discussion (Misc queries) | |||
quarterly reports | Excel Worksheet Functions |