none
Apply parameters from one page by another page RRS feed

  • Question

  • I am creating an excel power pivot report using a table.The report  contains the data like name of the projects.I want that when i click on the project name it will redirect to the another excel sheet and filter the project and other data related to that particular project in that sheet.Thanks in advance.
    Monday, September 5, 2016 7:26 PM

Answers

  • >>I am creating an excel power pivot report using a table.

    Is the table in the excel sheet or in the Power Pivot window?

    If the table is in the Power Pivot for Excel window, I suggest you export it into current worksheet.

     

    >>I want that when i click on the project name

    There is no click event for a single cell, however I think we could use Worksheet_SelectionChange event to execute the code.

    E.g.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Worksheets(1).Select
    a = ActiveCell.Value
    'clear filter
    If Worksheets(2).AutoFilterMode Then
    Worksheets(2).ShowAllData
    End If
    ' get the column index of the column Project
    For Each listCol In Worksheets(2).ListObjects("Table1").ListColumns
    If listCol.Name = "Projects" Then
    b = listCol.Index
    End If
    Next
    'apply filter
    Worksheets(2).ListObjects("Table1").Range.AutoFilter Field:=b, Criteria1:=a
    End Sub
    

    Tuesday, September 6, 2016 8:54 AM
    Moderator