Wasnt sure what to put in as the subject.

Sheet 1 is my compiled data sheet. I have 12 other sheets with each
representing that months data. Sheet 1 is linked to each months sheet picking
up all the totals for that month and putting them in a table.

Eg. Sheet 1 represents period 1
Sheet 2 represents period 2
and so on

In sheet 1 I have a number of lines referring to the end of period or has
the end of month date

Example

This data represents €¦€¦€¦€¦€¦. to the end of period 3 of FY 06-07
Comparison data for period 3 FY 05-06
or This data €¦€¦€¦. to 30 September 06


Instead of having to go through and manually change all these dates and
period numbers in sheet 1 at the end of each month I want to know how I can
have these lines automatically update at the end of each period.

Some thoughts to get this phrase done up auto:
or "This data €¦€¦€¦. to 30 September 06"

Assuming the phrase is to pick up data in A2 and then concat it with the
last day of the current month, then something like:

="This data " & A2 & " to " &
TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1,"dd mmmm yy")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lynda" wrote:
Wasnt sure what to put in as the subject.

Sheet 1 is my compiled data sheet. I have 12 other sheets with each
representing that months data. Sheet 1 is linked to each months sheet picking
up all the totals for that month and putting them in a table.

Eg. Sheet 1 represents period 1
Sheet 2 represents period 2
and so on

In sheet 1 I have a number of lines referring to the end of period or has
the end of month date

Example

This data represents €¦€¦€¦€¦€¦. to the end of period 3 of FY 06-07
Comparison data for period 3 FY 05-06
or This data €¦€¦€¦. to 30 September 06


Instead of having to go through and manually change all these dates and
period numbers in sheet 1 at the end of each month I want to know how I can
have these lines automatically update at the end of each period.

Thank you Max. That worked fine. Any ideas on how to have the period number
update. I'm sorry as i am quite a novice when it comes to excel.

"Max" wrote:

Some thoughts to get this phrase done up auto:
or "This data €¦€¦€¦. to 30 September 06"

Assuming the phrase is to pick up data in A2 and then concat it with the
last day of the current month, then something like:

="This data " & A2 & " to " &
TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1,"dd mmmm yy")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lynda" wrote:
Wasnt sure what to put in as the subject.

Sheet 1 is my compiled data sheet. I have 12 other sheets with each
representing that months data. Sheet 1 is linked to each months sheet picking
up all the totals for that month and putting them in a table.

Eg. Sheet 1 represents period 1
Sheet 2 represents period 2
and so on

In sheet 1 I have a number of lines referring to the end of period or has
the end of month date

Example

This data represents €¦€¦€¦€¦€¦. to the end of period 3 of FY 06-07
Comparison data for period 3 FY 05-06
or This data €¦€¦€¦. to 30 September 06


Instead of having to go through and manually change all these dates and
period numbers in sheet 1 at the end of each month I want to know how I can
have these lines automatically update at the end of each period.

"Lynda" wrote:
Thank you Max. That worked fine.

Glad to hear that.

Any ideas on how to have the period number update.

One way to get the period # for this kind of line auto:
.. "Comparison data for period 3 FY 05-06"

="Comparison data for period
"&VLOOKUP(TEXT(TODAY(),"m")+0,{1,1;4,2;7,3;10,4},2 ,0)&" FY 05-06"

I've assumed period 1 = Jan - Mar, 2 = Apr - Jun, 3 = Jul to Sep, 4 = Oct
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Correction to earlier formula. It should be:
="Comparison data for period
"&VLOOKUP(TEXT(TODAY(),"m")+0,{1,1;4,2;7,3;10,4},2 )&" FY 05-06"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Last line was left inadvertently incomplete.
It should read as:
I've assumed period 1 = Jan - Mar, 2 = Apr - Jun, 3 = Jul to Sep, 4 = Oct - Dec
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi Max, sorry for the delay in getting back to you, i got a bit busy with
other stuff and now there are a million other posts that you are probably
busy with.

That formula is doing what i want it to do and as hard as i study it i can't
see how you have done it. I did attempt to change the formula myself but all
i did was mess it up because i had no idea what i was doing.


My sheets work on periods per month. For this example we can say they are
pay periods. I am working on the Australian financial year calendar.

eg. Pay Period 2 (July) would cover the dates 12/7/2006 and 26/7/2006
Pay Period 4 (August) would cover dates 9/8/2006 and 23/8/2006
Pay Period 6 (September) would cover dates 6/9/2006 and 20/9/2006
Pay Period 8 (October) would cover dates 4/10/2006 and 18/10/2006
Pay Period 11(November) would cover dates 1/11/2006 and 15/11/2006 and
29/11/2006 (the period jumps from 8 to 11 because there are 3 pays in
that
month
and so it goes on right up to period 26 (June 2007)
I hope this makes sense Max. Thank you so much for your assistance.

Just quickly, why does the formula put that little box in front of the
period number?


"Max" wrote:

Last line was left inadvertently incomplete.
It should read as:
I've assumed period 1 = Jan - Mar, 2 = Apr - Jun, 3 = Jul to Sep, 4 = Oct - Dec
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---