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

[excel_learner]

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.

[Excel Fox]

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)

[excel_learner]

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]

Kindly assist on the above.

[excel_learner]

Guys, kindly help me on this, please.

[Admin]

Hi,

Run this script and create Pivot table.


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

[excel_learner]

Thanks, if I add another row (A6) with data what changes i will make? I mean how to select wider range?

[Admin]

Hi,


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

[excel_learner]

Thanks, but if i add columns after the "description" (lets say now 6 of them) column where do i change in script?

[Admin]

Hi,

See


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