Linking Sheets

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Oct 30, 2006.

  1. Guest

    Guest 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, Oct 30, 2006
    #1
    1. Advertisements

  2. Guest

    Guest 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")
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Lynda" wrote:
    > 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, Oct 30, 2006
    #2
    1. Advertisements

  3. Guest

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

    "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:
    > > 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, Oct 30, 2006
    #3
  4. Guest

    Guest Guest

    "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
    ---
     
    Guest, Oct 30, 2006
    #4
  5. Guest

    Guest 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"
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
     
    Guest, Oct 30, 2006
    #5
  6. Guest

    Guest 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

    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
     
    Guest, Oct 30, 2006
    #6
  7. Guest

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


    "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
    > ---
    >
     
    Guest, Oct 31, 2006
    #7
  8. Guest

    Roger Govier Guest

    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

    --
    Regards

    Roger Govier


    "Lynda" <> wrote in message
    news:...
    > 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 Govier, Oct 31, 2006
    #8
  9. Guest

    Max Guest

    Max, Oct 31, 2006
    #9
  10. Guest

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

    "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
    > ---
    >
    >
    >
     
    Guest, Oct 31, 2006
    #10
  11. Guest

    Roger Govier Guest

    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
    news:...
    > 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
    >> ---
    >>
    >>
    >>
     
    Roger Govier, Oct 31, 2006
    #11
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. cornel

    sheets in sheets

    cornel, Dec 16, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    6
    Views:
    152
    Harlan Grove
    Dec 16, 2003
  2. cornel

    Selecting sheets in sheets

    cornel, Dec 16, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    156
    Guest
    Dec 16, 2003
  3. Daniel
    Replies:
    1
    Views:
    416
    Dave Peterson
    Jul 6, 2005
  4. Lee Harris

    Locking Sheets / Hiding Sheets

    Lee Harris, Nov 29, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    211
    Arvi Laanemets
    Nov 29, 2005
  5. Dmitry
    Replies:
    6
    Views:
    244
    Dmitry
    Mar 29, 2006
Loading...

Share This Page