Excel experts, is this an impossible task?


  1. Posts : 5
    7
       #1

    Excel experts, is this an impossible task?


    Okay, this is a bit strange and I can't think of any way this could work, but maybe the internet can surprise me...

    I have data (text, not numbers) in Sheet1. I concatenate it into Sheet2 as part of a hyperlink. How can I make it so that the whole workbook is only Sheet2 with working hyperlinks? (Yes, I have a practical application of this)

    Example:
    Sheet1!A1 says: page
    Sheet2!A1 says: =HYPERLINK(CONCATENATE("http://www.com/",Sheet1!A1),Sheet1!A1)
    Sheet2!A1 actually reads: "page", and it has a hyperlink to "http://www.com/page"

    Now, how can I condense the workbook to ONLY Sheet2, maintaining not only the text referenced from Sheet1, but the working hyperlinks as well?
    So the workbook will have ONE page with cell A1 reading "page" and hyperlinking to "http://www.com/page"

    Does that make sense?
    Is it even possible?
    Last edited by amon41amarth; 29 Nov 2010 at 23:02. Reason: botched the hyperlink formula
      My Computer


  2. Posts : 5
    7
    Thread Starter
       #2

    SOLVED


    Solved on excelforum.com here.

    Here's the solution:

    Public Sub Example()
    Dim rngC As Range
    Dim lngEndPos As Long
    Dim strURL As String, strFriendly As String
    For Each rngC In Sheets("Sheet2").Cells.SpecialCells(xlCellTypeFormulas).Cells
    With rngC
    If UCase(Left(.Formula, 11)) = "=HYPERLINK(" Then
    lngEndPos = InStrRev(.Formula, ",")
    strURL = .Parent.Evaluate(Mid(.Formula, 12, lngEndPos - 12))
    strFriendly = .Text
    .Clear
    .Hyperlinks.Add .Cells(1), strURL, , , strFriendly
    End If
    End With
    Next rngC
    End Sub


    Thanks to DonkeyOte over there!
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd
All times are GMT -5. The time now is 01:48.
Find Us