Results 1 to 3 of 3

Thread: Conditional Filtering in EXcel 2007 Pivot Table

  1. #1

    Conditional Filtering in EXcel 2007 Pivot Table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I need to create a calculated field in Pivot table based on Date (Year&Month) and total number of clients for that Date. Thus, when Jan2012 selected from Filter, then the calculated field (denominator) should only pick total number of clients for Jan2012. I have two sets of data one for claims and one for clients. How do I create a Combo Box (or other any other methods) to perform the filtering. Below are samples of two tables + Final Pivot Table:Claim Table (Raw Data)

    YrMnth
    Paid
    201201
    $1200
    201202 $1600
    201203 $1500
    201204 $900

    Client Table (Raw Data)

    YrMnth Count
    201201
    10
    201201
    10
    201202
    20
    201203
    15
    201203 15
    201204 10
    201204 20

    Final Table (Pivot)
    Filter: YrMnth(Combo from Claim & Client Tables)

    Yrmnth Paid Paidd/Client
    201201 1200 1200/20=60
    201202 1600 1600/20=80
    201203 1500 1500/30=50
    201204 900 900/30=30


    Thank you

  2. #2
    Seeker jpr73's Avatar
    Join Date
    Apr 2011
    Location
    France
    Posts
    15
    Articles
    0
    Hi,
    See attached a proposal
    Consolidate the Claim and Client Table sheet with wizard pivot table
    Insert an element calculated for Paid/Client
    To get the correct values for field Paid/Client, the report filter Claim & Client should be set to all
    When adding or inserting data in tables don't forget to refresh the pivot table
    Hope this helps
    Best regards
    Attached Files Attached Files

  3. #3
    Thank you so much jpr. It worked great. Sorry for late thank you note..been on atrip for a while.

    Helal

Posting Permissions

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