Pivot table question

[Tigerexcel]

I thought this would be a pretty simple process but alas... I would like to work out how may staff are under 50 per dept, I'd usually do this through a value filter which in this case is not available via the menu for Age (because it is number data type?). Is there a workaround or does the data require some preparation for this to work?

Book7
	A	B	C	D	E	F
1	Name	Dept	Age
2	Jim	HR	62
3	Harry	Sales	31		Row Labels	Count of Age
4	Bert	Admin	25		Admin	2
5	Tom	HR	33		HR	3
6	Helen	Sales	54		Sales	3
7	Kate	Admin	31		Grand Total	8
8	Noel	HR	49
9	Steve	Sales	32
Sheet1

 

[Peter_SSs]

Add Age as a column label as well as in the values. Then you can filter the label for <50.

 

[Tigerexcel]

Thanks Peter for the quick response! Yes that works fine.

I notice that it works for Label filter but not for Value filter which is what I would have tried first seeing that Age is a number field.
Also is there any way of making this a little more dynamic for example what if I wanted to see how many are under 40 or between 45 and 55? Could I just reference a cell containing the number 40 etc, instead of having to type the value in the filter?

 

[Peter_SSs]

Also is there any way of making this a little more dynamic for example what if I wanted to see how many are under 40 or between 45 and 55? Could I just reference a cell containing the number 40 etc, instead of having to type the value in the filter?

Assuming that you wanted to keep it as a Pivot Table then you could with vba using a Worksheet_Change event code like this. I have used cell P1 as the age criteria cell.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rCrit As Range
  Dim myPT As PivotTable
  
  Set rCrit = Range("P1")
  Set myPT = ActiveSheet.PivotTables("PivotTable1")
  If Not Intersect(Target, rCrit) Is Nothing Then
    Application.EnableEvents = False
    myPT.PivotFields("Age").ClearAllFilters
    If Len(rCrit.Value) > 0 And IsNumeric(rCrit.Value) Then myPT.PivotFields("Age").PivotFilters.Add2 Type:=xlCaptionIsLessThan, Value1:=rCrit.Value
    Application.EnableEvents = True
  End If
End Sub

 

[Tigerexcel]

Thanks again Peter, I have modified your code to reference my Pivot table but it's not changing once I change the cell in P1. I'm a little new to Worksheet_Change event code but can't think of what else I need to do in order to get this to work.

 

[Peter_SSs]

I'm a little new to Worksheet_Change event code ...

Perhaps you have the code in the wrong place? It needs to be in that worksheet's module. To implement ..
1. Right click the Pivot Table sheet's name tab and choose "View Code".
2. Copy and Paste the code into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
(4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).)

 

[Tigerexcel]

Perhaps you have the code in the wrong place? It needs to be in that worksheet's module. To implement ..
1. Right click the Pivot Table sheet's name tab and choose "View Code".
2. Copy and Paste the code into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
(4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).)

Thanks Peter, all sorted now. I had placed it in the module but hadn't saved it as an xlsm file. Appreciate all the help you've provided on this question.

 

[Peter_SSs]

No problem, glad to help.

 

[Tigerexcel]

When I apply this to my actual data, the number of columns displayed is quite large, is there a way to collapse the columns so that only the grand total figures are displayed.

 

[Peter_SSs]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rCrit As Range
  Dim myPT As PivotTable
  
  Set rCrit = Range("P1")
  Set myPT = ActiveSheet.PivotTables("PivotTable1")
  If Not Intersect(Target, rCrit) Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With myPT
      .ColumnRange.EntireColumn.Hidden = False
      .PivotFields("Age").ClearAllFilters
      If Len(rCrit.Value) > 0 And IsNumeric(rCrit.Value) Then .PivotFields("Age").PivotFilters.Add2 Type:=xlCaptionIsLessThan, Value1:=rCrit.Value
      .ColumnRange.Resize(, .ColumnRange.Columns.Count - 1).EntireColumn.Hidden = True
    End With
    Application.ScreenUpdating = True
    Application.EnableEvents = True
  End If
End Sub