Results 1 to 10 of 10

Thread: Pivot Table assistance : (How to use column headers in report filter in PT)

  1. #1
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    14

    Pivot Table assistance : (How to use column headers in report filter in PT)

    I have set of data where i have budget and actuals where on left side i have the description and on the column headers i have the months (its month wise data in column), I want to use the column headers as Report Filter while creating the Pivot Table, how can i do it?

    I have attached the sample format of my data with requirement.

    Kindly assist.
    Attached Files Attached Files
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Instead of looking to Pivot it, why don't you use in B17 and drag across

    =INDEX($A$3:$BE$5,MATCH($A17,$A$3:$A$5,0),MATCH($A $11,$A$2:$BE$2,0)+MATCH(B$16,$B$1:$BE$1,0)-1)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    14
    Thanks for the reply. Actually i m trying to create dash board using slicers in pivot table and this pivot table is linked to the set of selected slicers. I gave the sample data to know how to use dates in report filter.
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!

  4. #4
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    14
    Kindly assist on the above.
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!

  5. #5
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    14
    Guys, kindly help me on this, please.
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    Run this script and create Pivot table.

    Code:
    Sub kTest()
        
        Dim ka, k(), i As Long, c As Long, n As Long
        
        ka = Range("a1:be5").Value     '<<<< adjust this
        
        ReDim k(1 To UBound(ka, 2) * UBound(ka, 1), 1 To 4)
        
        For i = 3 To UBound(ka, 1)
            For c = 2 To UBound(ka, 2)
                n = n + 1
                k(n, 1) = ka(i, 1)
                k(n, 2) = ka(1, c)
                k(n, 3) = ka(2, c)
                k(n, 4) = ka(i, c)
            Next
        Next
        
        With Sheets("Sheet2")
            .Range("a1:d1") = Array("Description", "Bud/Act", "Period", "Value")
            .Range("a2").Resize(n, 4) = k
        End With
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    14
    Thanks, if I add another row (A6) with data what changes i will make? I mean how to select wider range?
    Last edited by excel_learner; 03-12-2012 at 10:39 AM.
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!

  8. #8
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    Code:
    ka = Range("a1:be5").Value     '<<<< adjust this
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  9. #9
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    14
    Thanks, but if i add columns after the "description" (lets say now 6 of them) column where do i change in script?
    Attached Files Attached Files
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!

  10. #10
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    See

    Code:
    Sub kTest()
        
        Dim ka, k(), i As Long, c As Long, n As Long
        
        ka = Range("a1:be6").Value     '<<<< adjust this
        
        ReDim k(1 To UBound(ka, 2) * UBound(ka, 1), 1 To 10) 'adjust the '10'
        
        For i = 3 To UBound(ka, 1)
            For c = 8 To UBound(ka, 2) 'start from 8th column
                n = n + 1
                k(n, 1) = ka(i, 1) 'desc
                k(n, 2) = ka(i, 2) 'test1
                k(n, 3) = ka(i, 3) 'test2
                k(n, 4) = ka(i, 4) 'test3
                k(n, 5) = ka(i, 5) 'test4
                k(n, 6) = ka(i, 6) 'test5
                k(n, 7) = ka(i, 7) 'test6
                
                k(n, 8) = ka(1, c) 'bud/act
                k(n, 9) = ka(2, c) 'period
                k(n, 10) = ka(i, c) 'value
            Next
        Next
        
        With Sheets("Sheet2")
            .Range("a1:j1") = Array("Description", "", "", "", "", "", "", "Bud/Act", "Period", "Value")
            .Range("a2").Resize(n, UBound(k, 2)) = k
        End With
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Preparing Trial Balance Using Pivot Table
    By Zaigham in forum Excel Help
    Replies: 4
    Last Post: 05-14-2013, 11:57 AM
  2. Create a Pivot table
    By NITIN SHETTY in forum Excel Help
    Replies: 3
    Last Post: 01-26-2013, 11:01 AM
  3. Lookup and Count Using Pivot Table
    By RobExcel in forum Excel Help
    Replies: 2
    Last Post: 12-21-2012, 11:08 AM
  4. Pivot Table Count No of Items per Category
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 07-04-2012, 10:49 PM
  5. Filter more than one pivot table at one time
    By larryt1940 in forum Excel Help
    Replies: 8
    Last Post: 05-04-2012, 06:45 PM

Posting Permissions

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