How do I return another pivot table column from a user-selected (pivot column)?

  • Greetings

    I have a pivot table called "pt_UsagePS" as shown in the 1st graphic (upper right).


    Basically, If the user selects "74062 - FST FD/SNK BAR (cellT8)" from the combo box to follow, I want to store the "Row_ID (cell S8) for later use.

    As you can see at cell B15, I store the results of the user's selection.


    As you see, the combobox is displaying the four-items correctly from the "filtered pivot table"

    This is how I accomplished it in vba (with your prior help of course).

    Set PvtTbl = Worksheets("AdminCtrls").PivotTables("pt_UsagePS") 'Set creates an Object

    With PvtTbl

    .PivotFields("Facility Number").ClearLabelFilters

    .PivotFields("Facility Number").PivotFilters.Add Type:=xlCaptionEquals, Value1:=myfilter2

    End With

    Finally, How do I select the "Row_ID in vba so I can store it? Thanks.

  • Greetings

    Let me add one more item; some minor code from my combobox.

    1. Private Sub cbo_SelectUsage_Change() 'select pooled space usage type (CU Catcode)
    2. 'store "PS Main Usage Catcode" at worksheet cell
    3. Sheets("AdminCtrls").Range("B15").Value = cbo_SelectUsage.Text
    4. End Sub

    Notice how I stored the "combo box change event" to cell B15 (1st graphic, top left.)

    I want to be able to select the "Row_ID" just to the left of the users-choice, and store it also in cell B14.

    Any advise is greatly appreciated as I am really not getting the results desired, thanks.

  • I found a solution that does not involve vba at this point. using the Index-Match function at the worksheet.


    Which now stores the Row_ID value of "4591" at cell B14.

    I can now reference B14 in vba to populate a form. A happy ending.

  • But a new problem.

    Every time I select an item, the "Pooled Record" form changes with the selection; great, that's how it should work.

    Then with the form still open, when I go back to select another item; there's just three items.

    Go back again, there's two items, until just one item.

    Can anyone shed some light on this for me?

  • Greetings All

    I certainly do not expect miracles as we should all try to figure this stuff out for ourselves; the only way the lightbulb can flash.

    However, after 152 views; I though perhaps a comment or two could have come forward.

    In any event, you all be safe out there and wear your masks.