Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    I need some assistance concerning column running totals. When I searched the lounge I did not find an answer.

    I know a Pivot Table is the best way, but on this project it cannot be used.

    What makes this most difficult is that a User can insert a new row in the Cumulative Sheet for a current item which will the become part of next year’s cumulative totals.

    Also, a User can adjust prior year’s data which then must carry through each subsequent cumulative total column.

    What I am trying to do is to use the workbook year after year and keep running totals.

    I have attached a sample workbook.

    My solution is to use offset formulas below the data rows. When a new year begins I would copy the offset formulas and paste special (values and skip blanks) into the live data.

    Can anyone suggest a better way?

    Regards,

    Tom Duthie
    Attached Files Attached Files

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Hi Tom
    Is it a formula you are looking for or a better layout?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    I was hoping that a prior solution existed (without using a Pivot Table). This type of layout is common in businesses and was around well before Pivot Table.

    Any suggestions are appreciated. If a better layout will produce a way for Excel to keep running totals that is fine. If anyone knows of formulas that would yield a solution that would be great.

    I did consider using some type of Excel "List" approach but it seemed to have the same problems as the current sample workbook.

    Regards,

    Tom Duthie

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Argh! I'm behind a firewall today and I can't get your sample file. (so forgive me if I miss anything)
    But I have been thinking about your question.
    I'm not sure how I would improve your layout or your formulas. After all, you are getting the cumulative values by adding the current year with the previous years cumulative value. At this point (with this layout) an offset formula would only overly complicate things.
    I assume process-wise that when new data categories (rows) are added that you start to track them in their own unique row.
    I'm also assuming that when you set up for a new year you insert two columns (for the current/cumulative year values) and copy the formulas from the previous cumulative year leftward - so they should adjust appropriately. (note: the syntax =+c11+d11 should be =c11+d11 the extra plus symbol is a holdover from Lotus syntax)

    My solution is to use offset formulas below the data rows. When a new year begins I would copy the offset formulas and paste special (values and skip blanks) into the live data.

    Is it that you wish to convert the cumulative formulas into a value at some point?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Thank you for your comments and suggestions. Your solution and mine are quite similar.

    Process wise I will take the Offset formulas below the data (including the column headings and copy values skip blanks to the right of the data. I have set up Cumulative formulas in a bunch of columns right of the data so the shifted data will be included in cumulative totals I also put control totals for the Offset row data to ensure the live data and the cumulative total of any offset row data both agree. This allows the live formulas to remain to the left and for the new year data to populate the live data.

    As to the cumulative totals being changed to values, that has to be avoided because Users want to be able change prior data and see how if flows through to the current balances.

    This exercise does really show the great benefits of Using Pivot Tables, which unfortunately cannot be used for this project.

    Regards,

    Tom Duthie

Posting Permissions

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