+ Reply to Thread
Results 1 to 9 of 9

Retrieving data from two pivot tables based on date (grouped by month and year)

  1. #1
    Registered User
    Join Date
    04-30-2010
    Location
    Wales, Boyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    Retrieving data from two pivot tables based on date (grouped by month and year)

    Hi, Hope someone can help.

    I have a couple of pivot tables on individual worksheets.

    1. Sales forecast with item number and description as rows, columns are date grouped by month and year. The values are units forecast to be sold. The user currently enters a date range in a message box and I then run a sql query using the data as criteria. The forecast covers a period of about 6 months in the past up to about 18months in the future.

    2. Actual Sales. Same data but obviously we only have actual sales up to the current month.

    I need to retrieve the unit sales figure and unit forecast figure for each month and copy them across to a summary sheet one under the other. I cant for the life of me figure out an easy way to do this.

    The summary sheet needs 2 rows for each item. One with the forecast figure from the forecast pivot table for that month and one with the actual sales figure from the sales pivot table. there would then be columns for each month of data that I have.


    Can someone point me in the right direction of a function I can use to achieve this.

    Thanks in advance

  2. #2
    Registered User
    Join Date
    04-30-2010
    Location
    Wales, Boyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Retrieving data from two pivot tables based on date (grouped by month and year)

    Can anyone help with this one?

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Retrieving data from two pivot tables based on date (grouped by month and year)

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!

  4. #4
    Registered User
    Join Date
    04-30-2010
    Location
    Wales, Boyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Retrieving data from two pivot tables based on date (grouped by month and year)

    Quote Originally Posted by arthurbr View Post
    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!

    Cheers arthurbr will attach a file shortly

  5. #5
    Registered User
    Join Date
    04-30-2010
    Location
    Wales, Boyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Retrieving data from two pivot tables based on date (grouped by month and year)

    Hopefully the attached workbook will clarify the post a little.

    So, I need to copy values from the ActualsSummary and ForecastSummary to a CombinedSummary.

    I know I can do it manually using cell references but the date range varies between the two source spreadsheets. What I ideally need is to be able to copy the data column headings from the one with the most date columns and then bring across the relevant totals under each month from the two source spreadsheets into the combinedsummary sheet.

    Hopefully that makes sense!
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-30-2010
    Location
    Wales, Boyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Retrieving data from two pivot tables based on date (grouped by month and year)

    Just for additional clarification. The data in the two source summary sheets varies in length each time the report is run.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Retrieving data from two pivot tables based on date (grouped by month and year)

    Edit: are the sheets (Actuals / Forecast) Pivots or are you copying and pasting the Table output of each PT to these sheets as values only ?

    Below is based on the sheets being actual Pivot Tables...

    Given we don't have the data in Pivot Format it's hard to given specific advice but I would suggest looking at the GETPIVOTDATA function.

    The GETPIVOTDATA function will return an Error where no value exists for a given combination of field values - given use of XL2007 you can handle this quite easily by encasing the call within an IFERROR function.

    At a guess... based on your sample:

    CombinedSummary!B3:

    =IFERROR(GETPIVOTDATA("Invoiced Quantity",ActualsSummary!$A$1,"Budge Dimension 1 Code",LEFT($A3,FIND(" ",$A3&" ")-1),"Posting Date",MONTH(1&B$2),"Years",$E$1+(COLUMN(B2)>13)),0)

    edit: edit: you may have issues with the above given the inconsistencies between Row Field values and criteria for aggregation in the summary sheet (eg XMAS - which in theory would require a partial match / wildcard test on the PT data)
    Last edited by DonkeyOte; 05-04-2010 at 08:47 AM.

  8. #8
    Registered User
    Join Date
    04-30-2010
    Location
    Wales, Boyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Retrieving data from two pivot tables based on date (grouped by month and year)

    Thanks for the reply. I dont have the pivottables, they are driven by an sql query that takes forever to run and these summaries have their data manipulated by end users. e.g they might manually change some forecast values.

    Also, I need to be able to establish which of the summary sheets has the longest date period and then copy the date column headings from there to the combined summary sheet.

    I was hoping for some pointers on writing some vba that could locate the subtotal rows e.g, AYR and then cross reference this with the column heading e.g Jan10 and paste that value in the AYR, Jan10 cell in the combined summary.

    Ideally it would be a single function on the combinedsummary sheet that does something like Find the Row Label (i can stick this in its own column) so AYR for example. Find the column heading for the date so Jan 10 for example. And then use this to locate the value for Jan10 in the AYR total on forecast summary and the value for the AYR total for Jan10 from the actualssummary and paste these in the relevant places in the combinedsummary sheet.

    Sorry if its a wordy reply but I hope it makes sense


    Thanks again

  9. #9
    Registered User
    Join Date
    04-30-2010
    Location
    Wales, Boyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Retrieving data from two pivot tables based on date (grouped by month and year)

    Does my last post make sense?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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