Bring Only Filtered Data to Pivot Table

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Bring Only Filtered Data to Pivot Table

  1. #1
    Board Regular
    Join Date
    Oct 2008
    Location
    Phoenix
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Bring Only Filtered Data to Pivot Table

     
    How do you retain the data that is filtered in the main worksheet (we filter on 5 or more columns) when creating a Pivot Table?

    Are we having trouble because we are using a Shared Workbook?

    Doesn't the administrator (first person in) have rights to make all changes?

    If Shared Workbook restricts this, how can we get around this limitation?


  2. #2
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,629
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bring Only Filtered Data to Pivot Table

    Hello marylynn and Welcome to the Board!

    I'm not sure I entirely understand what you're trying to do, but here goes...

    Let's say your dataset cover Cells A1:E100. If your Pivot Table was built using these Cells as its source data, it doesn't matter whether you then filter on this source data - your Pivot Table will still pull in all of the information held there.

    Is there a reason you're not you're filtering on your source data and not within the Pivot Table itself?

    Matty

  3. #3
    Board Regular
    Join Date
    Oct 2008
    Location
    Phoenix
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bring Only Filtered Data to Pivot Table

    Thanks for the welcome!

    Our main spreadsheet contains 52+ columns where we filter out data from columns that we do not need in the pivot table. We want to keep the pivot table a simple summary, uncluttered if possible.

    I saw on another thread that Pivot Tables may not refresh with Shared Workbooks. Is bringing filtered data over to pivot tables another limitation of Shared Workbooks?

    Thank you.

  4. #4
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,629
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bring Only Filtered Data to Pivot Table

    Hello marylynn,

    I can't say I have ever used a Pivot Table within a shared Workbook, but I suspect problems will arise if you attempt to refresh the data when another individual is in that Workbook, potentially updating data that the PT is (trying) to use.

    Another trick I have recently learned is to use MS Query (which is built into Excel) to filter a large dataset so that your PT only displays the data you want to see.

    Have a look here:

    http://www.exceluser.com/explore/msquery1_1.htm

    Hopt this helps.

    Matty

  5. #5
    Board Regular
    Join Date
    Oct 2008
    Location
    Phoenix
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bring Only Filtered Data to Pivot Table

      
    Thanks, Matty. I will look into this next week.

    Bye for now...

User Tag List

Tags for this Thread

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