+ Reply to Thread
Results 1 to 3 of 3

pivot data from formulas

  1. #1
    Registered User
    Join Date
    10-24-2014
    Location
    Massachusetts
    MS-Off Ver
    2010
    Posts
    3

    pivot data from formulas

    I have a date range mm/dd/yyyy in field K2, and for the pivot table I need to be able to sort data by mm, quarter, and year (not all at the same time) in the spreadsheet I have formatting and formulas to create the columns of data:

    Start Date MONTH QUARTER YEAR
    3/31/2012 Mar Q1-2012 2012
    3/28/2012 Mar Q1-2012 2012
    1/4/2012 Jan Q1-2012 2012

    for the month =k2 and the format is changed to mmm
    quarter ="Q" &INT((MONTH(K2)+2)/3) & "-" & YEAR(K2)
    year =k2 and then format changed to yyyy

    When I select the month, quarter, and year for the pivot table it is only reporting back as the start date. I can change the formatting to show like above, but I cannot get the data results I am looking for, I am trying to get one report back for Jan, one for Feb etc...
    Instead it comes over as:
    Row Labels
    Jan
    Feb
    Feb
    Feb
    Mar
    Mar
    Mar
    Mar
    Mar
    Apr
    Apr
    May
    May
    May

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: pivot data from formulas

    Hi
    a Pivot Table does not need the extra columns for month year etc..
    Just use the dates as row lables, right click on them and select "Group ". Then select the grouping you want

  3. #3
    Registered User
    Join Date
    10-24-2014
    Location
    Massachusetts
    MS-Off Ver
    2010
    Posts
    3

    Re: pivot data from formulas

    Hi
    Thanks for the reply, I may not have been clear about what my final output is -
    this is the state of the pivot table
    Sum of Fee Column Labels
    Row Labels Chicago Chicago customText12 Europe South United States US (blank) Grand Total
    10/22/2014 58500 58500
    10/21/2014 60000 60000
    10/14/2014 36000 35000 71000
    10/10/2014 74383.8 74383.8
    10/9/2014 92400 92400
    10/3/2014 45000 45000
    10/2/2014 54600 48000 102600


    I need to have all date ranges, but only by month (a row for Jan, Feb, March) so the group option would not work for this table.

+ 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. Replies: 1
    Last Post: 03-26-2015, 10:33 AM
  2. Replies: 0
    Last Post: 08-14-2013, 10:39 AM
  3. Complex Request with Pivot Tables and copying pivot data to new page with formulas
    By Obsessed in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-07-2013, 09:16 AM
  4. Replies: 2
    Last Post: 05-08-2013, 04:56 PM
  5. [SOLVED] Pivot tables, sorting data into a useable way to use formulas against
    By Shane21882 in forum Excel General
    Replies: 0
    Last Post: 04-24-2012, 01:20 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