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


    [Blocked Image: https://www.ozgrid.com/forum/core/index.php?attachment/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.


    [Blocked Image: https://www.ozgrid.com/forum/core/index.php?attachment/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
    Private Sub cbo_SelectUsage_Change()     'select pooled space usage type (CU Catcode)
    
        'store "PS Main Usage Catcode" at worksheet cell
        Sheets("AdminCtrls").Range("B15").Value = cbo_SelectUsage.Text
        
          
    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!