Results 1 to 5 of 5

Pivot table to show Filtered Source Data

This is a discussion on Pivot table to show Filtered Source Data within the Excel Questions forums, part of the Question Forums category; Hi If I filter the data on my source data, Is it possible to have the pivot table NOT include ...

  1. #1
    Board Regular
    Join Date
    Oct 2006
    Posts
    319

    Default Pivot table to show Filtered Source Data

    Hi

    If I filter the data on my source data, Is it possible to have the pivot table NOT include the hidden rows resulting from the filter on the source data ?

    For example: if my source data is 1000 rows and 20 columns and I filter the data based on various conditions across the columns. I want to refresh the Pivot table and have it show results without the hidden rows.

    Thanks

    JVN

  2. #2
    Board Regular Yard's Avatar
    Join Date
    Nov 2008
    Location
    is always easier with the light on
    Posts
    1,857

    Default Re: Pivot table to show Filtered Source Data

    Not without an intermediate step.

    Silly question : why not just filter on your Pivot Table instead?
    "The old girl's chock full to the brim with fizz and ginger" - Bertie Wooster

  3. #3
    Board Regular
    Join Date
    Oct 2006
    Posts
    319

    Default Re: Pivot table to show Filtered Source Data

    Hi
    You are right . . . I would normally just filter on the pivot table.
    This particular pivot table that I created, though, I will be sending to a number of users to use. . . And when I was taking them through the spreadsheet and showing them how to use it, I got asked this question.

    So, I thought it would be interesting to find out if it is possible. I tried using a dynamic range as the source but it does not exclude the hidden rows.

    thanks for your reply

    JVN

  4. #4
    Board Regular Yard's Avatar
    Join Date
    Nov 2008
    Location
    is always easier with the light on
    Posts
    1,857

    Default Re: Pivot table to show Filtered Source Data

    Off the top of my head you would need to use the Worksheet_Calculate event in the source data worksheet to detect if an Autofilter had been changed (not an elegant solution IMHO), then use AdvancedFilter or something to copy the visible rows to another worksheet. Your Pivot Table would then have to be based on that other worksheet.

    Maybe there's a better way, but if there's no pressing need then life is too short - filter the Pivot Table!

    "The old girl's chock full to the brim with fizz and ginger" - Bertie Wooster

  5. #5
    Board Regular
    Join Date
    Oct 2006
    Posts
    319

    Default Re: Pivot table to show Filtered Source Data

    Agreed

    Thanks

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com