CODEWITHSUNDEEP
excelexcel-formula
Daimen
Daimen

Reputation: 73

Return a group of cells that match a drop down list

I am attempting to return a group of cells in a pivot table based on a drop down list in another workbook. In this example, When I select "Store 1", I would like if the entire sub table is returned e.g.

MESS 2 4534, NIGHT 2 4274, TOOLS 3 8123

I believe this is a sub row yet using function GETPIVOTDATA only likes to return specific values as when referencing the drop down list I can only pull the totals. Here is my pivot table.

enter image description here

Any help is appreciated.

RAWDATA (not an image):

STORE    ID       Category  Sum
Store 1           1 Tools         4050
Store 2           2 Food          4051
Store 3           3 Tools         4052
Store 4           4 Mess          4053
Store 5           5 Random        4054
Store 6           6 Mal           4055
Store 1           7 Night          200
Store 2           8 Releif        4057
Store 3           9 Test           154
Store 4          10 Tools         4059
Store 5          11 Food          4060
Store 6          12 Tools        54665
Store 1          13 Mess           454
Store 2          14 Random          45
Store 3          15 Mal           4064
Store 4          16 Night         4065
Store 5          17 Releif          45
Store 6          18 Test          1451
Store 1          19 Tools         4068
Store 2          20 Food          4069
Store 3          21 Tools           15
Store 4          22 Mess          4071
Store 5          23 Random        4072
Store 6          24 Mal           4073
Store 1          25 Night         4074
Store 2          26 Releif        4075
Store 3          27 Test          4076
Store 4          28 Tools           45
Store 5          29 Food          4078
Store 6          30 Tools           45
Store 1          31 Mess          4080
Store 2          32 Random          42
Store 3          33 Mal            523
Store 4          34 Night          453
Store 5          35 Releif           4
Store 6          36 Test           532
Store 1          37 Tools            5
Store 2          38 Food          4087
Store 3          39 Tools         2425
Store 4          40 Mess             2
Store 5          41 Random          45
Store 6          42 Mal              3

Upvotes: 1

Views: 149

Answers (1)

Mech
Mech

Reputation: 4015

This code looks for changes in workbook Dashboard, worksheet Summary, in cell E3 then changes the value of PivotTable pTbl using VBA.

When triggered, the VBA code will

  • clear the filters if they exist
  • filter the data with the contents of E3

To access VBA:

  • press alt-F11
  • expand Microsoft Excel Objects (left hand side)
  • locate the "Summary" worksheet

enter image description here

Find the name of your PivotTable:

  • click the pivot table
  • click the "PivotTable Analyze" tab
  • on the left, you will see "PivotTable Name:"
  • change pTbl (currently "PivotTable6") to this name

enter image description here

You can change the values of Dashboard, Summary, E3, and pTbl as needed.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wb As Workbook: Set wb = Workbooks("Dashboard")
    Dim ws As Worksheet: Set ws = wb.Worksheets("Summary")
    Dim trigRng As Range: Set trigRng = ws.Range("E3")
    Dim pTbl as String

    pTbl = "PivotTable6"
   
    If Not Application.Intersect(trigRng, Range(Target.Address)) Is Nothing Then
        ws.PivotTables(pTbl).PivotFields("Store").ClearAllFilters
        ws.PivotTables(pTbl).PivotFields("Store").PivotFilters.Add2 Type:=xlCaptionEquals, Value1:=trigRng.Value
    End If
End Sub

Upvotes: 1

Related Questions