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).


    1228213-pasted-from-clipboard-png


    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.


    1228214-pasted-from-clipboard-png


    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.


    Code
    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.


    =INDEX($S$5:$U$20,MATCH($B$15,$T$5:$T$20,0),1)


    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.