Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Monthly Automatic Updates (Excel XP)

    Hi everyone! I am trying to create a summary report in Excel XP from data taken from a number of worksheets.
    The catch is that I want the report to be automatically updated each month.
    For e.g. when I change the heading from Apr-04 to May-04, then May's data should be automatically populated in the summary.
    Please see the example file that I've attached.
    Does anyone know of a macro or function that will allow me to do this?
    Will definitely appreciate any suggestions.
    Thanks!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monthly Automatic Updates (Excel XP)

    If you put all of your data on one sheet, you could easily do what you want using a pivot table.
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Monthly Automatic Updates (Excel XP)

    Does this do what you want?

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monthly Automatic Updates (Excel XP)

    I agree with Legare <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> a redesign is the way to go if there are no serious constraints on layout.

    Long term - worksheet layout has more influence on useability than anything else and tricksie formulae are a recipe for confusion. Generally, if I have to use tricky formulae I'll include cross-check totals to verify that everything is included.

    A clean "data" worksheet with Pivot table reporting has the greatest chance of being error and bug free.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monthly Automatic Updates (Excel XP)

    Try the attached
    [Editted] All your dates were 1st of month except for January and February. The Summary sheet date will default to the first if you enter it as Jan 2004 and will therefore show a #NA error. There are three possible fixes - one use the 1st in other sheets, two use the actual date in the Summary, three change the match formula for approximation on the dates.

  6. #6
    New Lounger
    Join Date
    Apr 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monthly Automatic Updates (Excel XP)

    Thanks for the quick responses!!
    I understand that a re-design and pivot table may be less tricky, but the volume of data is great (I used a very simplified version of the actual workfile for my example). The data also spans multiple years. Placing all the data on 1 worksheet would be overwhelming for the users of this report.
    I will try the functions and give you an update on how it works.
    Thanks again,
    Michelle <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Monthly Automatic Updates (Excel XP)

    I agree with Legare's suggestion also. WIth a different setup, you have some builtin features to help users: you can use autofilter (data - filter - autofilter) to only view certain data, it allows use of the pivot tables.

    A better setup might even be:
    Date, category (apples, grapes, etc), item(apple pie, orange juice etc), Value

    THis wiould be the best way for the pivot.

    You could also help with data entry and extraction with the use of userforms for adding/editing/ and deleting of data.

    Steve

Posting Permissions

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