Force a hyperlink...

[tweek312]

I need a hyperlink function that Excel does not seem to support. The concept seems simple but execution has proven much more difficult than anticipated. Using this fairly simple lookup formula the value returned needs to be in the form of a hyperlink.

Formula:=IF(ISERROR(INDEX(Sites!A$1:B$23, MATCH(E3,Sites!A$1:A$23,), MATCH(K$2,Sites!A$1:B$1,))),"",INDEX(Sites!A$1:B$23, MATCH(E3,Sites!A$1:A$23,), MATCH(K$2,Sites!A$1:B$1,)))

Basically the formula returns an email address. Normally Excel is overzealous to convert email addresses to hyperlinks but in this case it seems that the lookup value has fooled Excel.

I have tried many things incuding the use of the HYPERLINK function and simply changing the lookup values to hyperlinks themselves.

I'm little stuck and could use some help.

All is appreciated.

Thx tW33k =D

 

[GlennUK]

You say:

I have tried many things incuding the use of the HYPERLINK function

... so what exactly happened when you tried that?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[tweek312]

Sorry.. should have elaborated.

I used many variations of the below formula.

=HYPERLINK(K9,"Click Here to Email")

K9 Would be where the email address is.

As you well know... this did not work; returns error "The address of this site is not valid. Check the address and try again."

I tried sticking a mailto: infront of the addy in K9; and tried putting the ref to K9 in quotes.

Argh!...

-tweek

 

[tweek312]

I worked it out in VBA... which i knew i could do.

I would still really like a Non-VBA solution but for now, this wanky little piece I wrote works for me.

Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

Range("L3").Select   'select the column you want
Do Until ActiveCell.Value = "STOP"
    
If ActiveCell.Value = "" Then
'remove hyperlink
    ActiveCell.Hyperlinks.Delete
    bourder 'calls bourder function
    ActiveCell.Offset(1, 0).Select
Else
    ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="mailto:" & ActiveCell.Value
    bourder 'calls bourder function
    ActiveCell.Offset(1, 0).Select
End If
Loop

Application.ScreenUpdating = True

End Sub
Function bourder()
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Function