Results 1 to 5 of 5
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Pivot Table Filter or Something Else? (2002)

    1) I am trying to create a pivot table with data that is within a rolling 12 months. It will be used once a month with new data for the new month so data will have to be refreshed. In the column field is a date. I
    You know it's time to diet when you push away from the table and the table moves.

  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

    Re: Pivot Table Filter or Something Else? (2002)

    You want
    today() not date()
    [but beware of leap years]

    You might check out how Joh Peltier defines dynamic ranges to plot the last 12 months. This technique of naming the range could be used to (instead of plotting) to define the range of the pivot table. If the range of the pivot is a dynamic range, when ever you refresh, it will do it based on the range which changes as the data changes.

    Steve

  3. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Pivot Table Filter or Something Else? (2002)

    Steve, thank you for your reply. I looked at Jon
    You know it's time to diet when you push away from the table and the table moves.

  4. #4
    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

    Re: Pivot Table Filter or Something Else? (2002)

    I don't think that you can create a pivot with filtered data as the source. The source would be the entire range not just the visible cells.

    You can copy the filtered dataset to a new sheet and create the pivot from this. You could also use a pivot based on the whole dataset and then use page fields as a filteriing device. The page fields work similar to the filters and allow interaction with the table.

    Steve

  5. #5
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    341
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Pivot Table Filter or Something Else? (2002)

    Hi Steve. Yes I think you are right. I don't think I can create a pivot table base on filtered source. I did manage to use advanced filters and copy the data (only the last 12 months worth) to another spreadsheet. Then I took that data and created a pivot table. Turned out really well. Thanks again.
    You know it's time to diet when you push away from the table and the table moves.

Posting Permissions

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