Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Location
    The Okanagan, Br. Columbia, Canada
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is This Possible (N/A)

    Hello there,

    I was wondering if any of you "resident experts" can tell me if what I am wanting to do is possible with Excel.

    I have setup a spreadsheet that tracks donations for an organization I belong to (donations occur every Sunday) and this is what I was hoping Excel could do for me.

    Right now I have a year to date average formula (=AVERAGE(B2:M6)) tracking weekly donations which results shows up in cell N8 on one sheet.

    What I was hoping to accomplish is that when I input another weekly total of donations that Excel would copy the previous average found in cell N8 to another worksheet under 1st Sunday Average before it automatically updates cell N8 with the new average reflecting the newly entered donations.

    If this is possible I would then have this occur for every Sunday's donations and then have the results show up on the new sheet under 2nd Sunday Average, 3rd Sunday Average and so forth.

    For everyones information this is how the spreadsheet is laid out:

    From b6:m6 is where the weekly updates are stored.

    b2:b6 is January's 1st Sunday, 2nd Sunday, 3rd Sunday, 4th Sunday
    c2:c6 is February's 1st Sunday, 2nd Sunday, 3rd Sunday, 4th Sunday
    d2:c6 is March's 1st Sunday, 2nd Sunday, 3rd Sunday, 4th Sunday
    and so forth ending up with December being entered into m2:m6.

    I was also thinking that if it was possible for Excel to ask me which sheet along with the cell of where I wanted the old average results pasted in that would be nice in case I decide to move things around a bit (different sheet names and/or cells).

    I am hoping that someone can understand where I am coming from as I am sure this might be quite confusing.

    I appreciate any input.

    RGDS & TIA

    Curtis M.

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

    Re: Is This Possible (N/A)

    I would use a database-like table, with all records below each other. That makes it a lot easier to do all kinds of calculations, filtering etc. To keep track of the YTD average for each Sunday, you won't need any code, it can be done with simple formulas.

    In the attached workbook, the dates are in A2:A53, the amounts in B2:B53 and the YTD averages in C2:C52. I entered the formula =AVERAGE($B$2:B2) in cell C2, and filled down to C53. In the range $B$2:B2, the first cell is absolute (signified by the $ signs), so it will remain the same as you fill down; the second cell is relative, so it will be changed to B3, B4 etc. as you fill down.

  3. #3
    New Lounger
    Join Date
    Dec 2004
    Location
    The Okanagan, Br. Columbia, Canada
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is This Possible (N/A)

    Hans,

    I appreciate all your work unfortunately I have to stick to this layout due to policies in the organization. Is your way the only way that I can achieve what I am looking to do?

    RGDS

    Curtis M.

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

    Re: Is This Possible (N/A)

    Would it be acceptable to use a data table as in the workbook I attached, plus another worksheet that uses formulas to present the data in the form you described?

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is This Possible (N/A)

    Here's an extension of Han's attachment.
    Now that I see the newest replies, this probably wont work in your case, but
    if something like it helps well good.

    I think that in order to make something fit your pre-established format you would have
    send usa sample with names and amounts garbled for privacy.
    We would also need to see what it should look like when finished - what it is you hope to automate

    Let us know

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is This Possible (N/A)

    I was on this same track, and agree with Hans that this would be a preferred layout to use. I added a "week" column, too, and used pivot tables to get the report in the layout originally described. I also made a pivot table off Hans' Average column to track the running YTD average. The last tab is how I envisioned the 'required' layout to be based off the original post's description of the data.

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

    Re: Is This Possible (N/A)

    Thanks, GoCush and BAN. I hope that Curtis can use this - it's looking great now.

Posting Permissions

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