Linking Sheets

[Guest]

Wasn’t 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.

 

[Guest]

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

---

 

[Guest]

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.

 

[Guest]

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

---

 

[Guest]

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"

---

 

[Guest]

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

---

 

[Guest]

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?

 

[Roger Govier]

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 Insert>Name>Define
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

 

[Max]

Roger, many thanks for the help !

Lynda, you're in good hands with Roger's response

---

 

[Guest]

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.

 

[Roger Govier]

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.