Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi everyone,

    I haven't had much experience with pivot tables, and have been "playing" with a workbook (attached) in order to calculate the difference between two sets of data.

    I have created a calculated item to determine the movement of data from one year to the next. But when I add this item to the pivot table, it tries to determine the movement for all account numbers (data) and descriptions (data) with lots of rows calculating as zero. I merely want a single movement calculated for each account number & description and not the various combinations of each. It appears that my problems start once I add more than one field in the "row" section of the table. The description field is there merely to make the report more readable.

    In the attached workbook, I have a worksheet showing my preferred layout, which I hope explains what I am trying to achieve.

    Thanks,

    Matthew

    [attachment=83199:Sample_P...t_Report.zip]
    Attached Files Attached Files

  2. #2
    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
    You can add an autofilter to the pivot table and autofilter when the 2007 or 2008 data is non-blank.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could add a calculated column to the source data to combine the account number and description, and use that in the pivot table. See the attached version: [attachment=83200:Sample_P...t_Report.xls]

    Or use Microsoft Access...
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='768983' date='03-Apr-2009 13:54']You can add an autofilter to the pivot table and autofilter when the 2007 or 2008 data is non-blank.

    Steve[/quote]


    Thanks Steve,

    The autofilter (Data | Autofilter) is grayed out whenever I select the pivot table. Is there something that needs to be enabled in the pivot table to allow autofiltering?

    Regards,

    Matthew

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='768991' date='03-Apr-2009 16:58']You could add a calculated column to the source data to combine the account number and description, and use that in the pivot table. See the attached version: [attachment=83200:Sample_P...t_Report.xls]

    Or use Microsoft Access...[/quote]


    Thanks Hans,

    I was hoping to keep the account & description fields separate as I will be using them in another worksheet. However I can split them apart in that worksheet, so this appears to be my best solution.

    Regards,

    Matthew

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't think you can autofilter within a pivot table.

  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
    [quote name='mkeyser' post='769492' date='06-Apr-2009 23:50']The autofilter (Data | Autofilter) is grayed out whenever I select the pivot table. Is there something that needs to be enabled in the pivot table to allow autofiltering?[/quote]

    Sorry, I forgot that it takes a "trick". Select the blank cell to the immediate right of the header row (where the autofilter is to be added). Then Data - filter - autofilter and it will put the filter in the pivot table.

    Steve

  8. #8
    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
    [quote name='HansV' post='769500' date='07-Apr-2009 02:14']I don't think you can autofilter within a pivot table.[/quote]

    See Post 769533 for a "trick"

    Steve

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That's a neat trick - I hadn't seen that.

    But I don't think it does exactly what Matthew wants - when you hide the rows without data for 2007 or 2008, you also hide some of the account numbers in column A.

  10. #10
    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
    You can keep the separate columns and add the combined one in the dataset and then use the separate ones as desired in other sheets and the and the combined one in the pivot

    Steve

  11. #11
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='769644' date='08-Apr-2009 09:41']You can keep the separate columns and add the combined one in the dataset and then use the separate ones as desired in other sheets and the and the combined one in the pivot

    Steve[/quote]


    Steve & Hans,

    Thank you for your help. At the moment I have used Hans combined account & description field to give me one line per account number. It is achieving what I need.

    The Autofilter trick looks promising & I will have to look how I can use it in this worksheet.

    Regards,

    Matthew

Posting Permissions

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