Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filters on Pivot Tables Excel 2010

    I have a query that I update monthly. I have pivots wrapped around the data to look at specific elements of the data each month. I have noticed when I bring in new data, the filters no longer seem to work correctly.

    I check the filters and they are still turned on and say may be filtered to look for the word "filter" but instead the data it is pulling in does not contain the word "filter".

    Is there anything I can do to ensure the filters work even though the data is refreshed monthly?

    Thanks.

  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
    Have you refreshed the pivot table before using the filters?

    If so, could you provide a sample worksheet with a couple sets of data and walk us through what you do and what happens?

    Steve

  3. #3
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Leaving filters applied, as I'd like for them to remain consistent from period to period, I:
    1. Refresh query which refreshes source data.
    2. Go to worksheet with Pivot Table and click on Data, Refresh, Refresh All.
    3. This is when my data gets out of whack. The filter is still applied; however, the data it brings back isn't the correct data. Again, if I have it filtered to only show items containing the word "filter", I check and the filter is still applied but the returned data is words, etc., that contain stuff besides "filter."

    If this doesn't make sense, I will attach an example while back at work tomorrow.

  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
    I cannot replicate the problem in XL2010 in my example, so my dataset my not be representative or I am not doing the what you do. I am not using a query, I am just copying an alternate dataset over the source to represent a new dataset from refreshing the query.

    Steve

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Trying to preserve existing filters after 'refreshing' data can be unreliable depending on the type of filters you are using.
    For example selected ranges (xlTop10Items, xlBottom10Items, xlTop10Percent, xlBottom10Percent) appear to be treated as fixed values rather than say, top10.
    I would recommend clearing ALL filters before refreshing the data, and then use vba to re-apply your preferred filter conditions.

    zeddy

Posting Permissions

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