Hyperlinks in Pivot Tables

[Countess]

I am working on a Pivot Table and need to keep the hyperlinks from the original data active and working. I found an answer on some site stating highlight the cells in question, to go to the PivotTable Toolbar, Properties, Format and select “Display as hyperlink” however there is no such ‘properties’ section I can find on Excel 2010.


Thanks for your assistance.

 

[sdsurzh]

Hi,


http://office.microsoft.com/en-us/excel-help/use-a-pivottable-report-to-make-external-table-data-available-in-excel-services-HA010207979.aspx


Try this.


Thanks,

Suresh Kumar S

 

[Countess]

Thanks Suresh, but it looks like the answer is no - hyperlinks do not work in pivot tables. If you meant to direct me to another part of the document, please be specific where.

 

[bobhc]

Good evening Countess


Have you clicked on the cell in your table and right click from drop down select hyperlink and the format link as you need, for that is what I do, I have a table with a column of names and I have given each one an email hyperlink, you can choose to hyperlink to another document.

 

[Countess]

Hi Bobhc,

While I see that option in a regular table, it is not offered that readily in a "pivot" table. Thanks for trying.

 

[bobhc]

Good evening Countess


My apologizes for the post, I am thinking of a "plain" table. I have looked at the full blown pivot table made with the data from Plain table,.....and I have tried every thing and I can not find any where for a hyperlink, I think there is at times some confusion with the insert tab as to what is an is not a "pivot Table".

Can you not set up the hyperlinks in the original data to update that data and then from there to update your Pivot table, slicers and all

 

[Countess]

Sorry, but no Bobhc. We need to have the hyperlinks from the orginal data work in the pivot table. Since that doesn't seem to be an option, we are lookin at other ideas. Thanks again for trying.

 

[Virtual]

Hi, Does anyone has answer of this question? To rephrase the question - How to add hyperlinks to a pivot table in Excel 2010 ?

 

[SirJB7]

@Virtual

Hi!

Does this help?

http://www.linkedin.com/groups/Is-it-possible-create-interactive-44008.S.61021031

http://www.mrexcel.com/forum/excel-questions/447028-sidestep-pivot-table-create-hyperlink.html

And there are more from where I copied them... Google, you know?

Regards!

 

[Virtual]

Thanks for trying. But it did not help. I am looking for a simple function to convert Pivot table texts to hyperlinks.

 

[SirJB7]

@Virtual

Hi!

I'm afraid it doesn't exist. If you happen to find it please post it here. Thank you.

Regards!

 

[Virtual]

Thanks for trying.

 

[SirJB7]

Hi, Virtual!

Glad to help, sorry you couldn't solve as you wanted by formulas. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!

 

[Kimberly Slaton]

So I found a way to be able to get your hyperlinks to work in a pivot table. There may be a simpler solution, but this is working for me at the moment. If you do have a simplified version, please share

so i have a sheet1 that has 3 original columns:

text value id
a 1 32811
b 2 12345
c 3 67890
d 4 258011

The id's have hyperlinks to them. I use a VBA code to extract the urls into the next column.

Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub

Run the macro on your current worksheet (sheet1)

You will notice that the urls don't have the hyperlink format, but when you double click them they will create a hyperlink. However, you do not have to do this in order to have the urls work in your pivot table.

Then i create my pivot table on a new worksheet (sheet2) and bring in my id and url column. You will then do another VBA code on the pivot table sheet (sheet2)

Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ptc As PivotTable, Value As Variant, Rng As Range
If Target.Cells.Count = 1 Then
    For Each ptc In ActiveSheet.PivotTables
        If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
            If Left(Target.Value, 7) = "http://" Then
                ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
            End If
        End If
    Next ptc
End If
End Sub

Run the macro on current worksheet (sheet2). You then should be able to click the url in your pivot table and it should work. I hope this helps