Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,536
    Thanks
    38
    Thanked 68 Times in 64 Posts

    Copy text and hyperlink

    I have copied cells using INDIRECT(formula) and only the text appears in the target cell.
    The original cells are hyperlinked to URLs. I'd like the new cell to have the original text AND also be hyperlinked.

    For example, A1 might have GOOGLE as text in the cell and it's linked to Google.com URL.
    In cell C1, I want it to read GOOGLE but also have that cell linked to the same link as in A1.

    There are hundreds of rows to which this has to apply. The indirect formula is used because the rows are every 8th row in blocks of data, so I created a formula to grab the cell.

    Even using: =INDEX($A$2:$A$41,ROWS(C$2:C6)*8-8+COLUMNS($C6:C6)) I don't get the URL hyperlink copied...just the text.

    Can this be done without a macro?
    Last edited by kweaver; 2015-01-07 at 18:28.

  2. #2
    Lounger
    Join Date
    May 2014
    Posts
    45
    Thanks
    0
    Thanked 2 Times in 2 Posts
    There is a =Hyperlink () function; if you are able to have the bare URLs available, you'd just point to the cell containing the URL. Other than that, seems like you might be out of luck. I tested it to see if Hyperlink () will retrieve a link but it won't, at least not in 2013.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,789
    Thanks
    139
    Thanked 705 Times in 639 Posts
    KW,

    You could have a formula in cell B1: =HYPERLINK(GetHlink(A1), "Go Here")

    that calls a UDF that returns the hlink address and assigns it to the cell:

    Code:
    Public Function GetHlink(rng As Range)
        GetHlink = rng.Hyperlinks(1).Address
    End Function
    HTH,
    Maud

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,536
    Thanks
    38
    Thanked 68 Times in 64 Posts
    Thanks, Maudibe...I guess there's not formula equivalent to the UDF, so that's my only option?!?!

    Scott: Thanks; I know about HYPERLINK but in this case the cell isn't an email address nor a web address per se.
    It's a link. So, hyperlink function doesn't do the job here. Seems to need a macro or UDF.

Posting Permissions

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