Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 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 19: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,639
    Thanks
    115
    Thanked 651 Times in 593 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,470
    Thanks
    30
    Thanked 62 Times in 58 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
  •