+ Reply to Thread
Results 1 to 3 of 3

VBA to Pivot by Month and Cumulative Year to Date, then include variance field

  1. #1
    Registered User
    Join Date
    02-21-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    64

    VBA to Pivot by Month and Cumulative Year to Date, then include variance field

    Hi all,

    I've pretty much exhausted all other avenues and internet searches, and desperately need some advice/help please?

    I’ve created a Pivot Table based on several months (periods) data. However, try as I might through various other snippets of code, I’ve been unable to create a pivot table showing the current period (based on a user-changeable range on Sheet1 called “period”) as well as a cumulative year to date position (in this case periods 201301-201309), split by two column labels.

    The attached workbook shows a simplified pivot and macro. What I’m trying to achieve here is instead of a Grand Total Column, have YTD cumulative columns split by Retainer Articles Used, Retainer Expenses and Retainer Fees.

    THEN, and this is the even trickier bit (at least I feel it is): there is a budget column in the source data tab (Download2). I want to do a variance between the total amount for the period and the total budget, rather than have a budget column against each individual column.

    I.e. as it currently shows, keep column E,F,G,H exactly as they are, then insert a budget column in I, but without splitting the budget into Retainer Articles etc etc… and then insert a calculation field to work out the variance between the Period Total and the budget column.

    Long shot, but could anyone help on either one of these two problems, are they even possible??

    Thanks in advance,

    Mike
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: VBA to Pivot by Month and Cumulative Year to Date, then include variance field

    We gonna try to answer them one by one.

    Here the solution for running cummulative year to date.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    02-21-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: VBA to Pivot by Month and Cumulative Year to Date, then include variance field

    Hi Oeldere,

    Many thanks for your reply, however I needed something more dynamic with periods across the top of the page instead of down, so had to find a work around.

    Thanks to another problem I was working on, I came up with a workaround that met my needs and seems to work just fine. I've attached this, please feel free to review it and let me know if you have any thoughts/improvements/suggestions

    Thanks again for your efforts, much appreciated!

    Cheers,

    Mike
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to add Month-on-Month and Year-on-Year %Variance into a pivot table
    By emeritus1812 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-09-2013, 02:37 AM
  2. [SOLVED] cumulative comparison with last year of month to date
    By johnbtor in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 09-04-2012, 10:38 AM
  3. Replies: 1
    Last Post: 02-09-2012, 04:13 AM
  4. Replies: 1
    Last Post: 01-17-2012, 06:00 PM
  5. [SOLVED] Converting a date field into a month-year only field
    By C.Hirsch in forum Excel General
    Replies: 1
    Last Post: 04-24-2006, 07:15 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1