Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting a list with a hyperlink field (XL2000)

    I have a long (11,000+ entries) data list in Excel with a column of hyperlinks. The hyperlinks are URLs attached to a text entry, not just the URL itself. I want to sort the list on a text field. When I do this the hyperlink field APPEARS to have been sorted properly but it seems that it is only the text that has been sorted - the hyperlinks remain fixed to the cell where they started. This results in a list that is sorted but the text is pointed to the wrong URL!! Help!!

    Paul

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting a list with a hyperlink field (XL2000)

    The way to imagine is that the "hyperlink object" (the hyperlink itself) is attached to the cell itself. When you sort, the text inside the cell is sorted. The cells are not physically sorted, just the "contents" of the cells. Thus the attached objects stay where they are.

    I think the best way around this is to use the HYPERLINK function. The function can be linked to a cell contents so when the cells are sorted the function will sort along with the contents and thus the link will also be sorted.

    <pre>=HYPERLINK(URL,Display)</pre>


    Steve

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a list with a hyperlink field (XL2000)

    Steve,
    Thanks for that. Is there any way I can automate the entry of this function into a new column or do I need to go thru it by hand ? I have some familiarity with VBA but I am not having any success in extracting the URL from a cell. Any help ?

    Paul

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting a list with a hyperlink field (XL2000)

    Try this on a backup copy. It will change all hyperlinks in the worksheet to the function using the current link as the "URL" and the current value as the display value. It will then delete the hyperlink, leaving the function.

    Steve

    <pre>Sub TransformHL()
    Dim HL As Hyperlink
    Dim rCell As Range
    For Each HL In ActiveSheet.Hyperlinks
    With HL
    Set rCell = .Range
    rCell.FormulaR1C1 = "=Hyperlink(" & _
    Chr(34) & .Name & Chr(34) & ", " & _
    Chr(34) & rCell.Value & Chr(34) & ")"
    .Delete
    End With
    Next
    End Sub</pre>


  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a list with a hyperlink field (XL2000)

    Many thanks for your help!!

    Paul

Posting Permissions

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