Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi to the Excel gurus...

    Gotta begin by saying, WOW, what a difference. Gonna take a little getting used to...

    On to my issue: I have a user who utilizes a synchronized list in Excel from Sharepoint. One of the fields in the list contains hyperlinks to various internal web sites.

    However, when the user creates a pivot table from the list, the pivot table shows the text of the hyperlink, but there is no pivot table.

    I'm pretty sure I read a while back that Excel pivot tables do not include live hyperlinks from the data source. I am writing here to see if that is correct, or if, in fact, there is a way to capture the live hyperlinks within the pivot table.

    This question applies to both Excel 2003 and Excel 2007.

    As always, thank you in advance...

    George

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I have a hard time visualizing what you're talking about - could you attach a sample workbook (with sensitive information removed or altered)?

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='768328' date='30-Mar-2009 15:18']I have a hard time visualizing what you're talking about - could you attach a sample workbook (with sensitive information removed or altered)?[/quote]

    My apologies, Hans. In my original message I stated that there was "no pivot table". What I meant to say was there was "no hyperlink, just text".

    Does that make it any clearer?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='BrookBoy' post='768330' date='30-Mar-2009 21:26']Does that make it any clearer?[/quote]
    No, sorry.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, I have created a small sample for you to view.

    Note that in column B of the data, each cell contains a hyperlink (at least it does here).

    Now note that in the pivot table to the right, that field contains text instead of the hyperlink.

    Is there any way to have the pivot table show the hyperlink, or is this something that pivot tables just will not do? Again, this question applies to both Excel 2003 and Excel 2007.

    And again, thank you for all your efforts. Very much appreciated.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The items in a pivot table are just text, even if the source data contain hyperlinks. From Use a PivotTable report to make external table data available in Excel Services:
    ... active hyperlinks are not supported in PivotTable cells. The hyperlink is treated as text, but you cannot follow the hyperlink to a Web page or document...

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='BrookBoy' post='768337' date='30-Mar-2009 15:44']OK, I have created a small sample for you to view.

    Note that in column B of the data, each cell contains a hyperlink (at least it does here).

    Now note that in the pivot table to the right, that field contains text instead of the hyperlink.

    Is there any way to have the pivot table show the hyperlink, or is this something that pivot tables just will not do? Again, this question applies to both Excel 2003 and Excel 2007.

    And again, thank you for all your efforts. Very much appreciated.[/quote]

    I dont know about Excel 2007. However, in Excel 2003 and prior, I don't beleive you can do this. A Pivot Table is desiged to work using data supplied by a User. In your case the Hyperlinks are data just like the other data and numbers. When you generate a Pivot Table it is extracting data, not formatted data.

    Once a Pivot Table has extracted data the User must tell the new Pivot Table how to format the extracted data. The Pivot Table Tools do not allow for formatting Hyperlinks.

    Perhaps VBA could get around this but I would think not.

    Regards,

    Tom Duthie

  8. #8
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Hans. I had thought so, but could not find an authoritative source.

    Very much appreciated, as always...

  9. #9
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='duthiet' post='768342' date='30-Mar-2009 15:59']I dont know about Excel 2007. However, in Excel 2003 and prior, I don't beleive you can do this. A Pivot Table is desiged to work using data supplied by a User. In your case the Hyperlinks are data just like the other data and numbers. When you generate a Pivot Table it is extracting data, not formatted data.

    Once a Pivot Table has extracted data the User must tell the new Pivot Table how to format the extracted data. The Pivot Table Tools do not allow for formatting Hyperlinks.

    Perhaps VBA could get around this but I would think not.

    Regards,

    Tom Duthie[/quote]


    Hi Tom,

    Thanks for the update. The source that Hans sent applies to Excel 2007 and does state directly that pivot tables do not support active hyperlinks, so if there is a workaround, it would have to be in VBA.

    Regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •