Pop-Out Tables for relevant data

[k______S]

Hello,

I apologize in advance because my question is going to be vague. A year or so ago a collegue sent me an excel workbook which had the ability to click on certain fields wihtin the summary page and the data related to that cell would break out in a new table on a new tab.

I have a list of claims with member ID #'s. Each member might have anywhere from 1-30 claims and the member ID# field is consistent on each claim line.

I have created a summary tab that, amongst other metrics, counts the number of claims and displays this at the member level.

Is there any functionality which allows me to click on the member ID# on this summary page and a table including only the claims associated with that members is broken out?

Any help is appreciated!

 

[mrshl9898]

Sounds like the file your collegue sent contained a Pivot Table, giving that ability.

You could use VBA to go to the data and filters by the member ID once you double click on a member ID.


In the below i'm assuming both Membership numbers are in column A, and the data is on a sheet called "Data".

The macro needs to go in the worksheet module.

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


    Dim ssheet As Worksheet
    Dim dsheet As Worksheet
    Dim Memno As String


    Set ssheet = ActiveSheet
    Set dsheet = Sheets("Data")   'change to name of sheet containing the data


    If Target.Column = 1 Then   'change 1 to column number in the Summary containing Membership number
    Memno = ActiveCell.Value
    dsheet.Select
    dsheet.Range("A1").AutoFilter Field:=1, Criteria1:=Memno     'change 1 to column number in the Data containing Membership number
    End If


End Sub