Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Filter a new Pivot in another Worksheet

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Let's say I have a Worksheet called "Data" with data (duh!), in Worksheet "PivotA" I have a pivot table based on the data in Worksheet "Data". I have another Pivot in worksheet "PivotB" which is also based on the data in worksheet "Data".

    Now in PivotB I only want data where one of the fields is greater than 1000. (SalesAmount > 1000).

    Can I filter out data for PivotB, while not affecting PivotA?

    Thanks,
    Terry

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Terry

    Not too sure I have understood you but it sounds like you have Pivot Table "A" based off Pivot Table "B"? While this does save memory they are no longer completely seperate. Just create your Pivot Table "B" from the same data range rather than Pivot Table "A"

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, I'll expand a bit let's say my data is

    Salesman Sales
    Terry 1000
    Bob 500
    Andy 250
    Mary 2500

    PivotA is based on all the rows,

    PivotB is based on rows where Sales>500, so:
    Salesman Sales
    Terry 1000
    Mary 2500


    Thanks,

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You have confused me even more! If your tables are seperate one will NOT effect the other.


  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry Dave, I often confuse myself . Ignore everything I've said up to now, answer this:

    Is it possible in a Pivot Table to filter out data based on a formula. For example, only bring in customers that have Sales>500.

    Sorry about the confusion and thanks for your quick reponses.

    Terry

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Arrh! In that case you should only need to place the Sales field in the inner most field and then Group it. You will only be able to do this if all you fields in sales contain numbers. In other words blanks anywhere in the Data and/or blanks rows in the Pivot data reference will prevent this. Dynamic ranges are very good for Pivot Tables and I have many examples here:

    http://www.ozgrid.com/Excel/DynamicRanges.htm

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-05 22:08, bochaton wrote:
    Ok, I'll expand a bit let's say my data is

    Salesman Sales
    Terry 1000
    Bob 500
    Andy 250
    Mary 2500

    PivotA is based on all the rows,

    PivotB is based on rows where Sales>500, so:
    Salesman Sales
    Terry 1000
    Mary 2500


    Thanks,
    Too easy!! Just add another field (column) to your data set, 'Sales>500', using the formula, =Sales>500. Configure 'Sales>500' as a PAGE field in PivotB and set its value to TRUE.

    [ This Message was edited by: Mark W. on 2002-03-06 10:59 ]

Some videos you may like

User Tag List

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
  •