Limit pivot pull-down choices to non-zero results?

M

Momo

New Member
Joined
May 12, 2005
Messages
8
I have a fairly complex Pivot table and Pivot Chart that cover labor costs in different categories for a multinational. There is a hierarchy in the choices I provide in the pull-down menus. The hierarchy is Country - Business Unit - Function, using three side-by-side pulldowns. However, there are a large number of functions (30), though each Country/BU combination only has a few (always less than 10).

Is there any way to have the "empty" subsets not appear as a menu choice?

If relevant, the underlying data worksheet is not very big. About 900 lines and 18 columns.

Momo
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
When you say pulldowns are you referring to those in the PivotTable or Comboboxes? If in the PivotTable are they Page fields or something else?
 
Upvote 0
It should be possible to toggle the Visible property of the PivotItems, but first can you post a sample of your data and say what you have in the data field?
 
Upvote 0
Here is the way the data is structured. The point is, as an example, that the MS-D function below only exists within the MS Business. However, it is still visible as a choice when a different business is selected. I would like it to disappear from the page field "Function" pulldown if no record exists for that combination. I can't see any way of doing it, but don't claim to be a super-expert type.

Country Business Function Jan Feb Mar
UK CS HR 97.7 70.5 13.6
UK MS HR 155.9 11.1 47.2
GY MS MS-D 74.0 110.6 48.4
FR ES Fin 53.4 35.2 88.7
FR ES HR 16.6 13.0 167.7
FR MS Fin 97.3 77.3 10.8
IT ES Fin 104.1 183.0 197.8
IT MS MS-D 14.4 84.4 50.7
AU CS Fin 131.0 60.4 125.9
AU CS HR 16.0 15.3 37.2
AU MS MS-D 159.5 20.4 45.0
SP MS HR 48.7 123.7 10.5
SP MS Fin 18.8 182.2 62.0
SP CS HR 39.2 188.4 150.4
SP ES Fin 118.4 34.5 48.8
 
Upvote 0
I don't think you can change what appears in the PivotTable dropdowns. But you could use ComboBoxes from the Control Toolbox to get what you want. Interested?
 
Upvote 0
Quick learner...

Don't know how to do that, but am a quick learner. Will try hard.

Momo
 
Upvote 0
Right click any Toolbar and check Control Toolbox. Click the ComboBox icon and click and drag in your worksheet to position and size it. Repeat until you have 3 of them. Right click the worksheet tab and choose View Code. Paste this code into the window on the right:

Code: 
Private Sub ComboBox1_DropButtonClick()
    Static DisableCB1 As Boolean
    Dim PItem As PivotItem
    Application.ScreenUpdating = False
    If DisableCB1 = False Then
        ComboBox1.Clear
        ComboBox1.AddItem "(All)"
        For Each PItem In Worksheets("Sheet1").PivotTables("PivotTable1").PageFields("Country").PivotItems
            ComboBox1.AddItem PItem.Name
        Next PItem
        DisableCB1 = True
    Else
        DisableCB1 = False
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub ComboBox1_Change()
    If ComboBox1.ListCount = 0 Then Exit Sub
    Application.ScreenUpdating = False
    Worksheets("Sheet1").PivotTables(1).PageFields("Country").CurrentPage = ComboBox1.Value
    Application.ScreenUpdating = True
End Sub

Private Sub ComboBox2_DropButtonClick()
    Static DisableCB2 As Boolean
    Dim Sh As Worksheet
    Dim PT As PivotTable
    Dim PF As PivotField
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Page1 As String
    Dim PItem As PivotItem
    Dim Formula As String
    Dim ShowItem As Boolean
    Set Sh = Worksheets("Sheet1")
    Set PT = Sh.PivotTables("PivotTable1")
    Set PF = PT.PivotFields("Business")
    Set Rng1 = Sh.Range("A1:A1000")
    Set Rng2 = Sh.Range("B1:B1000")
    Page1 = PT.PivotFields("Country").CurrentPage.Name
    Application.ScreenUpdating = False
    If DisableCB2 = False Then
        ComboBox2.Clear
        ComboBox2.AddItem "(All)"
        If Page1 = "(All)" Then
            For Each PItem In PF.PivotItems
                ComboBox2.AddItem PItem.Name
            Next PItem
        Else
            For Each PItem In PF.PivotItems
                Formula = "=SUMPRODUCT(--('" & Sh.Name & "'!" & Rng1.Address & "=""" & Page1 & """)"
                Formula = Formula & ",--('" & Sh.Name & "'!" & Rng2.Address & "=""" & PItem.Name & """))>0"
                ShowItem = Evaluate(Formula)
                If ShowItem = True Then
                    ComboBox2.AddItem PItem.Name
                End If
            Next PItem
        End If
        DisableCB2 = True
    Else
        DisableCB2 = False
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub ComboBox2_Change()
    If ComboBox1.ListCount = 0 Then Exit Sub
    Application.ScreenUpdating = False
    On Error Resume Next
    Worksheets("Sheet1").PivotTables(1).PageFields("Business").CurrentPage = ComboBox2.Value
    Application.ScreenUpdating = True
End Sub

Private Sub ComboBox3_DropButtonClick()
    Static DisableCB3 As Boolean
    Dim Sh As Worksheet
    Dim PT As PivotTable
    Dim PF As PivotField
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Rng3 As Range
    Dim Page1 As String
    Dim Page2 As String
    Dim PItem As PivotItem
    Dim Formula As String
    Dim ShowItem As Boolean
    Set Sh = Worksheets("Sheet1")
    Set PT = Sh.PivotTables("PivotTable1")
    Set PF = PT.PivotFields("Function")
    Set Rng1 = Sh.Range("A1:A1000")
    Set Rng2 = Sh.Range("B1:B1000")
    Set Rng3 = Sh.Range("C1:C1000")
    Page1 = PT.PivotFields("Country").CurrentPage.Name
    Page2 = PT.PivotFields("Business").CurrentPage.Name
    Application.ScreenUpdating = False
    If DisableCB3 = False Then
        ComboBox3.Clear
        ComboBox3.AddItem "(All)"
        If Page1 = "(All)" Then
            If Page2 = "(All)" Then
                For Each PItem In PF.PivotItems
                    ComboBox3.AddItem PItem.Name
                Next PItem
            Else
                For Each PItem In PF.PivotItems
                    Formula = "=SUMPRODUCT(--('" & Sh.Name & "'!" & Rng2.Address & "=""" & Page2 & """)"
                    Formula = Formula & ",--('" & Sh.Name & "'!" & Rng3.Address & "=""" & PItem.Name & """))>0"
                    ShowItem = Evaluate(Formula)
                    If ShowItem = True Then
                        ComboBox3.AddItem PItem.Name
                    End If
                Next PItem
            End If
        Else
            If Page2 = "(All)" Then
                For Each PItem In PF.PivotItems
                    Formula = "=SUMPRODUCT(--('" & Sh.Name & "'!" & Rng1.Address & "=""" & Page1 & """)"
                    Formula = Formula & ",--('" & Sh.Name & "'!" & Rng3.Address & "=""" & PItem.Name & """))>0"
                    ShowItem = Evaluate(Formula)
                    If ShowItem = True Then
                        ComboBox3.AddItem PItem.Name
                    End If
                Next PItem
            Else
                For Each PItem In PF.PivotItems
                    Formula = "=SUMPRODUCT(--('" & Sh.Name & "'!" & Rng1.Address & "=""" & Page1 & """)"
                    Formula = Formula & ",--('" & Sh.Name & "'!" & Rng2.Address & "=""" & Page2 & """)"
                    Formula = Formula & ",--('" & Sh.Name & "'!" & Rng3.Address & "=""" & PItem.Name & """))>0"
                    ShowItem = Evaluate(Formula)
                    If ShowItem = True Then
                        ComboBox3.AddItem PItem.Name
                    End If
                Next PItem
            End If
        End If
        DisableCB3 = True
    Else
        DisableCB3 = False
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub ComboBox3_Change()
    If ComboBox1.ListCount = 0 Then Exit Sub
    Application.ScreenUpdating = False
    On Error Resume Next
    Worksheets("Sheet1").PivotTables(1).PageFields("Function").CurrentPage = ComboBox3.Value
    Application.ScreenUpdating = True
End Sub

Press Alt+F11 to return to your worksheet. Click the design icon at top left in the Control Toolbox to exit design mode. Try it out by selecting different items in each of the Comboboxes.

Note: When I tested this, some of the items in the PageField dropdowns became blank. I don't know what causes this, but I hope it works for you.
 
Upvote 0
Thanks...

Thanks a lot. It will be Monday before I can try it out. Gotta go climb an Alp today.

Momo
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Pivot Table Row Label Filter based on another Pivot Table
Replies
1
Views
541
Koxeida
K
M
  • Question
Excel VBA, Hidden IE Select Pull-Down Menus and AJAX:
Replies
0
Views
3K
MattDW
M
D
  • Question
Update several worksheet tabs based on input in the first tab.
Replies
0
Views
358
dekadez
D
X
  • Question
Pivot pull-down shows non-existing choices
Replies
4
Views
1K
XL_sunshine
X

Forum statistics

Threads
1,215,523
Messages
6,125,321
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top