Link to home
Create AccountLog in
Avatar of chris_desborough
chris_desboroughFlag for Australia

asked on

Hyperlinks in Pivot tables

I've checked for this question on this site and others and have conflicting answers so just wanted to clarify.

I have a pivot table where I have a code for each client their respective rows in a pivot table.  I have other demographic info that I also want to display about these clients but I don't want to nest the information in the table because it becomes to messy to look at so can I hyperlink the code int he table which will correspond to the client details in another sheet in the workbook?
Avatar of dlmille
dlmille
Flag of United States of America image

Guidance on PivotTables and hyperlinks:

http://support.microsoft.com/kb/317630 suggests you can.

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

However, you can determine for yourself whether hyperlinks will work.

See attached.  Select on a hyperlink in the table, then hit CTRl-r which should run the ExecuteHyperlink() subroutine.

You should have enough here to get going what you want.

I also took over the double-clik on the sheet with the sheet with the pivottable - you could check to see if the selection is in the right range, then make the proc() call.  I'm being expeditious because I think you have everything you need, now.

And didn't have to create a class module taking over the Pivottable (that could be an improvement for coolness!)
so - select the text for a hyperlink (you could format the pivottable item as underline, blue or whatever) and hit ctrl-r, or just double-click it.

The code is in Module 1 for the hyperlink lookup and follow. The trigger code is in Sheet4 - where the Pivot table is.

Enjoy!

Dave


PivotHyperlinksBent.xlsm
Avatar of chris_desborough

ASKER

This is cool but not what I'm really after.  The first column in the pivot table will have a code that relates to the clients information i.e name, address, etc in another sheet in the same workbook so I was thinking that if I can click the code in the first column of the pivot table it would take me to the row on the worksheet with the client details.  

Is this possible?
That's easy too, using the same logic - please send a short example and I'll BEND your Pivot Table to your will!

Dave
Hi,

I've attached a basic file with sample data but it's pretty much what I'm after.  In sheet 1 is the pivot table.  In sheet 2 will be the demographic data.  In workbook I'm using there will be 11 columns each with specific demographic data relating to the code (i.e first name, surname, address 1, suburb, etc) in the pivot table if this makes a difference.  However all I need is to click the code in the pivot table to take me to the code on the details sheet.  
Hyperlink-file.xlsx
ok - give me 5 mins and when you click code DEF34 in the pivot table, it will navigate you to cell A2 in Sheet 2 - is this correct - what you want?

Dave
That's what I'm after.  Cheers
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Dave you're a champion.  Many thanks
Actually I have one last question, will this search for the code on the data links sheet regardless of where it is in the first column.  For example - If DEF34 is in the 2nd row of the pivot table but is on the 3rd row of the data sheet will the hyperlink still go to the correct code on the data sheet?  Cheers
Yes - it searches through the entire data sheet until it finds the match.  If there is more than one match, it takes you to the last match.

Dave