Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limiting Data in a Pivot Table? (Excel XP)

    Hey all,

    Let me start off by saying I don't know if this is possible, I'm an Access person, so that's where I'm coming from...

    So I have some data in a worksheet, and I would like to create two separate pivot tables. I've summarized the difference between the two proposed pivot tables in one field, which contains an A, B, or C. In Pivot Table 1, I want the results of only records that meet A, and in Pivot table 2, I want the results of records that meet A & B combined. I'm trying to do this with ony one list of data, but is it not possible?

    The problem is that I can only figure out how to do this two ways, neither of which is the way I want. If I do it with a Page Field (the row across the top of the page), I can only select A or B or C for the entire pivot table. If I do it with a Row Field, I get a column that I don't want, and I don't want A, B, and C to show up in separate rows, I want the combined total for A & B.

    Do I need to have two different datasources for these pivot tables (ie. one with A, and the other with A&B combined)? Or is there a way to limit the overall pivot table without showing that field in the table?

    Hope this makes sense,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limiting Data in a Pivot Table? (Excel XP)

    If I understand your description correctly, I'd add a calculated column to the source data with formulas that return TRUE if your summary column contains A or B, FALSE otherwise. You should be able to use this as page field in your second pivot table.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting Data in a Pivot Table? (Excel XP)

    Hi Hans,

    Always saving me, eh? After I thought about it, I did what you said (before you said it) because I think you may have told me the same thing a long--long time ago. Somehow I never remember how to use Excel lol. But you always save the day ;-)

    Intuitively, though it would make more sense (at least for us Access people) if you could query within excel [img]/forums/images/smilies/wink.gif[/img]

    Thanks!!! as always,

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,813
    Thanks
    132
    Thanked 479 Times in 456 Posts

    Re: Limiting Data in a Pivot Table? (Excel XP)

    Hi Hans

    Perhaps instead of adding a column to the source data (not always possible) you could do this using the 'multiple items' feature for the pivot page field.

    A page field typically has either one pivot item or (All).
    To get a page field to have multiple items:
    1. drag the page field into either the row or column area of the pivot report.
    2. after you have moved it there, use the drop-down to check the multiple items you want.
    3. after choosing your items, drag the field back to to the page field area of the report.
    4. voilla!
    The page field will now show (Multiple Items)

    Note: unfortunately you need to repeat this process if you want to change the selections.


    zeddy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limiting Data in a Pivot Table? (Excel XP)

    I get the impression from Cecilia's description that she wants fixed selections, so your suggesion would be a good way to do it!

Posting Permissions

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