Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Chain Excel Sheets (any)

    I have a file with each sheet representing a week. B6 of the Week1 is a beginning value. C6 thru G6 are values for Monday thru Friday and H6 is the total. B6 of the Week 2 (the next sheet) references H6 on the Week 1 sheet. Week 3!B6 references Week2!H6. Each sheet's sequence pulls the total from the previous sheet. All sheets are arranged exactly alike, with the same 7 active locations: the total from the previous week, 5 values, and a new total which will be used in the next sheet.

    I have to set up a full year with this format. Is there a way to streamline chaining one sheet to the next without manually setting up the reference in each sheet? If I could set up one and group the remaining sheets and paste into them with a relative formula, my life would be easier.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Chain Excel Sheets (any)

    I'd prefer to enter all data in a single sheet, one row per day. You can then use a pivot table to summarize the data by week.
    If you prefer the current setup, you could use the INDIRECT function to refer to the previous week's worksheet provided that you name the sheets consistently. For example, if the worksheets are named Week1, Week2, ..., you could do the following:
    - Enter the week number in a cell, say A1.
    - Group the sheets for weeks 2 through 52.
    - Enter the following formula in Week2!B6:

    =INDIRECT("Week"&(A1-1)&"!H6")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •