Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi loungers....I have a quick question....... I know how to hyperlink to various things such as webapges, documents etc etc in Excel....however, I am using a formula that 'pulls' an email address into a cell (courtesy of Don Wells....thank you for that Don)........it pulls the address in from another worksheet, where it behaves like typical email address (ie: you can click it and send an email to the address)....however, on the worksheet where the data is pulled INTO the email address shows up but is not active (ie: it doesn't open an email)......if I try to hyperlink it, the best I can seem to do is to link it to the other worksheet containing the names and email addresses.........I suspect that it doesn't perform in the conventional way b/c it is 'really' a formula (??) but wondered if there is a work-around ?...thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Let's say you have an e-mail hyperlink in A1 on Sheet1.
    In cell A1 on Sheet2 you have a formula =Sheet1!A1
    This cell will *not* behave like a hyperlink.
    To make it behave like a hyperlink, change the formula to =HYPERLINK(Sheet1!A1)

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Hans....on sheet 'list', I have a list of the names, numbers & email address....on sheet 'work', I use the following formaula: =IF($L13="","",INDEX(Agents!D,MATCH($L13,Agents!$B:$B,0)))

    Agents!d:d is the list (in col D) on the List! page of email addresses......if I use my existing formula and add HYPERLINK (as in: =IF($L13="","",Hyperlink(INDEX(Agents!D,MATCH($L13,Agents!$B:$B,0)))), when it draws the email address into the sheet 'work' it looks like it is linked (and the little hand shows up as usual) but when I click the address to start an email, it says "Cannot open specified file" as an error message........

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    =IF($L13="","",HYPERLINK("mailto:"&INDEX(Agents!D,MATCH($L13,Agents!$B:$B,0))))

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Hans....actually, I had previously tried that but all it did was insert 'mail to:' in front of the email address....but it doesn't 'activate' the email address.....

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm sorry, I have no idea why it doesn't work for you. It does for me.

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='dmcnab' post='784119' date='10-Jul-2009 23:10']Hi Hans....actually, I had previously tried that but all it did was insert 'mail to:' in front of the email address....but it doesn't 'activate' the email address.....[/quote]
    Remove any spaces from the formula. 'mail to:'
    Regards
    Don

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='wdwells' post='784194' date='12-Jul-2009 14:13']Remove any spaces from the formula. 'mail to:'[/quote]
    Good catch!

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='dmcnab' post='784119' date='10-Jul-2009 23:10']Hi Hans....actually, I had previously tried that but all it did was insert 'mail to:' in front of the email address....but it doesn't 'activate' the email address.....[/quote]
    You will probably wish to format these cells with the Hyperlink style.
    Regards
    Don

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='784080' date='10-Jul-2009 17:45']Try

    =IF($L13="","",HYPERLINK("mailto:"&INDEX(Agents!D,MATCH($L13,Agents!$B:$B,0))))[/quote]

    Thank you for that Hans. I was achieving the same results with a macro imposing a speed and maintenance penalty.
    Regards
    Don

Posting Permissions

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