ExcelBanter - Linking Sheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Linking Sheets (https://www.excelbanter.com/excel-worksheet-functions/116627-linking-sheets.html)

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

Hi Lynda

In case Max has missed this, as he would normally be on line before now.

Just quickly, why does the formula put that little box in front of the
period number?
It's probably an Alt+Enter or line feed that has been inserted in the
formula to wrap it in your email client.
In the formula bar, if the text is wrapped onto 2 lines, just delete at
the end of line 1 to bring it onto a single line and the character
should disappear.

To deal with your period dates, set yourself up a table such as
11/07/2006 0
26/07/2006 2
23/08/2006 4
..
..
26/06/2007 26

Name this table as Dates using InsertNameDefine
Then amend Max's formula to
="Comparison data for period "&VLOOKUP(TODAY(),dates,2)&" FY 06-07"

For me it returned
Comparison data for Period 8 FY 06-07

--
Regards

Roger Govier

"Lynda" wrote in message
...
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
---

Roger, many thanks for the help !

Lynda, you're in good hands with Roger's response
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thank you Roger that worked.

Thank you also Max for fixing the first part of my problem.

Keep up the good work guys your assistance is invaluable for dummies like me.

"Max" wrote:

Roger, many thanks for the help !

Lynda, you're in good hands with Roger's response
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Lynda

You're very welcome. Thanks for the feedback, but all credit is due to
Max, who, I'm sure ,is very appreciative of your comments.

--
Regards

Roger Govier

"Lynda" wrote in message
...
Thank you Roger that worked.

Thank you also Max for fixing the first part of my problem.

Keep up the good work guys your assistance is invaluable for dummies
like me.

"Max" wrote:

Roger, many thanks for the help !

Lynda, you're in good hands with Roger's response
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---