Results 1 to 5 of 5
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Every 13 weeks I set up a new workbook to keep track of financial results. Each week has its own worksheet, named wk1, wk2, wk3, etc.

    For my own good reasons, I only create a new worksheet at the beginning of the week concerned. For example, during week 4, the workbook will contain sheets wk1, wk2, wk3, and wk4, but no worksheets for weeks 5 and beyond. I will add sheet wk5 at the start of week 5.

    Each sheet contains four named cells with totals for the week to which it applies. The names are the same for all the sheets: MATotal, MBTotal, PATotal, PBTotal. The totals in each cell change during the week, but are stable once the week is over.

    I want to establish four cells on each sheet, each of which holds the sum of those four cells for all the worksheets to date. It seems as though a simple total for sheets wk1 through wk13 won't do the job, since sheet wk13, for example, doesn't exist until week13 rolls around.

    If I had to, I could accept a situation where the totals in the new cells for existing sheets would change when new sheets are added. In other words, where during week 4, sheet wk4 would hold the totals for weeks 1-4, but when sheet wk5 were added, it would hold the totals for that week, too.

    Can I do what I want, and if so, how?

    PS - I'm thinking that if there were some sort of "IF EXIST" function for worksheets, I could do what I need. Maybe a little VBA is called for. I have some modest VBA skills, but they are very rusty.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts
    I needed to something similar, and what I did was to go ahead and create the weeks, then I just hid them until needed. Simple but effective.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  3. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I've thought about that, but the benefit of having the totals is outweighed by the benefit of creating each weekly sheet when it's needed.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Have you checked out Data>Consolidate ?

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Lou,

    If you can live with an empty worksheet, which you could hide, and a summary sheet to tally the MATotal, MBTotal, PATotal, PBTotal figures, a 3D formula might be the way to go.

    The idea would be to position an empty worksheet after your wk1 worksheet. That way, the 3D formula in the summary sheet will automatically update to reflect the tallies from your wk1 sheet and whatever worksheet(s) are located between that and the empty one. For example, suppose in week 1 you have three worksheets: wk1, Empty, Summary. The formula '=SUM(wk1:Empty!A1:A1)' in the Summary worksheet will pick up whatever's in A1 in both the 'wk1' and 'Empty' worksheets. When you come to week 2, insert the wk2 sheet between the 'wk1' and 'Empty' worksheets, so you have four sheets arranged: wk1, wk2, Sheet2, Summary. A1 from 'wk2' now gets added to the Summary sheet's total.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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