Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine Pivot with Autofilter (XL97 SR2)

    Is it possible to use Autofiltered data as the source for a Pivot Table?

    Here's the data layout. Columns for: Class, Date, AccumulatedHours, RepairCosts. The data has Autofilter applied so I can view all the records of a particular Class and with AccumulatedHours less than a specified amount.

    I have added a SUBTOTAL function so that I can see the Sum of RepairCosts for the currently-displayed records. The SUBTOTAL function repects the current Autofilter, and operates only on the displayed records (in contrast to the SUM function).

    What I would like to do is make a Pivot Table that also respects the current Autofilter, however, I haven't seen a way to do that. In other words, I would like to Pivot only on data where AccumulatedHours < xxxx. The only alternative I can think of is to copy all the filtered records to a temporary location and build the Pivot Table on the copied data.

    Is there a way to combine the Autofilter with the PivotTable?

    Thx.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    gilraneg
    Guest

    Re: Combine Pivot with Autofilter (XL97 SR2)

    I would add a column to the data table the pivot table is connected to. Do your test in that column ( If (AccumulatedHours < xxxx, "Pass", "Fail"). Add the field related to that new column to the page section of your pivot table and filter on pass or fail.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine Pivot with Autofilter (XL97 SR2)

    So simple. So cool!
    Thanks.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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