Internal Hyperlinks in Pivot Tables

[prometeusz]

Hi


I know it is possible to do: see the first file attached. As you click on the pivot table cells, you get directed to the relevant tabs.


Have tried to achieve this in my file, but for some reason, the same trick does not appear to work - see "zero test 2" file.


The vba code involved is as follows:

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)








    Dim pvtTable As PivotTable
    Dim shtTemp As Worksheet
    
    If Target.Cells.Count = 1 Then
        For Each pvtTable In Target.Parent.PivotTables
            If Not Intersect(pvtTable.RowRange, Target) Is Nothing Then
                On Error Resume Next
                Set shtTemp = Worksheets(Trim(Target.Value))
                On Error GoTo 0
                If Not shtTemp Is Nothing Then
                    Application.Goto shtTemp.Range("A1")
                End If
            End If
        Next
    End If
End Sub

What difference makes it impossible in zero test 2 file to achieve the same result? It is not because the source and the pivot table are separate. I would hope to complete the task by Christmas, but anything later than that will still be great because the file will be developed on a continuous basis.


Many thanks
T

[Paul_Hossler]

I'm not understanding

If I click on the PT entry ...

Delay in undertaking supervisions, completing records in timely manners

The selection change macro tries to go to a sheet by that name. Since there isn't, it just does nothing

[prometeusz]

That would explain..

[edit]

Now I realise the problem. The VBA takes the text string in a clicked pivot table cell and assumes it stands for the tab name, selects the tab and goes to a1.

The trick I seek to achieve is for the pivot table entries check the link the source cell has and go there...
Is this possible at all? How to achieve that?

Many thanks

Tom

PS. I attach zero test 3 file.

[Paul_Hossler]

Maybe it's possible, but not sure if it would help

Seems like it would be very dependent on the PT options (Subtotals, Grand Totals, Details, no Details, etc.)


Look at the simple attached example

Sheet 'PT' - the PT in col H has TX summed into a single entry (YELLOW)

That represents 4 dates - col B (ORANGE)


Look at the 'Data' sheet and TX has 10 entries (GREEN)


If you click on the YELLOW, what would you want to show or happen?

If you click on one of the ORANGE what would you want to show or happen?


BTW, if you double click on one of the blue cells in the PTs, you get a new sheet with the drill down of data that went into that cell (Sheet1 and Sheet2)

[prometeusz]

Thanks for that, Paul.

At least, it is better to know it is impossible and focus on what is to be done.

Here is the deal.
This is supposed to be a problem register. I have removed all the data and the slicers, but basically, the pivot table is supposed to show problems based on their severity. Expanding a problem description reveals two more cells - a more detailed description of the negative consequences and blank cell left for manager's comments.

Given the limitations of inputting data directly into a pivot table, I would hope there is a quick way to access the comment cell in the source table. I have set things up so that this would be instantly reflected in the pivot table, however, the problem is how to get to the relevant cell.
There are going to be around 50 entries, so anything other than a hyperlink means searching, which is not too elegant a solution.

This is why I was hoping clicking on a pivot cell would lead to the source cell. If this won't work with excel, be it.

Thanks all the same.
Tom

[Paul_Hossler]

Well, there's a lot of clever people here

Maybe someone will come up with a suggestion for you

[prometeusz]

Cool. Thank you Paul. You have saved me hours of hitting the wall.

[Paul_Hossler]

Only other thought would be to


1. Date/Time stamp each data sheet entry when it's added (macro can do that)


2. Use a UserForm the opens when you double click a Dashboard item (no PT, just a filter/sorted list)


3. Capture comment, save to data sheet