Hyperlink Issue

[bdumaguina]

Hello Guys and Gals,

I have this concern, hope you guys can help out with it.

So I have this database of clients in one worksheet with all kinds of information about them. It's in a table format (ctrl+T), so it's quite easy to navigate through the clients and update the information, dealings and negotiations happening with them.

In another worksheet I was able to set up a kind of 'company search' thing using look up functions, index and match stuff - the purpose of this worksheet is to extract the four (4) latest 'happenings' or the 4 latest things that has happened with a certain company from the database.

NOW, my concern is in some of the entries I have for a company; I have hyperlinks to a specific website. The hyperlinks work fine on the database, BUT they don't work anymore when they are extracted into the 'company search' worksheet. I guess the hyperlink is attached to the cell reference of the database sheet.

Is there a way for me to extract the hyperlink too?

Thanks in advance, much appreciated.

 

[moonfish]

If your hyperlinks are stored within a separate cell (e.g. they are always in column F of source data), you can turn them into hyperlinks by enclosing the extracted result with the HYPERLINK() function.

 

[Jon von der Heyden]

With a stepped approach, you should use a helper column in your lookup table to house the URL. If this is a big job to go through then you can use the UDF below.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Index</td><td style=";">Text</td><td style="background-color: #FFFF00;;">Hyperlink</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-decoration: underline;color: #0000FF;;">Excel Guru</td><td style="background-color: #FFFF00;;">http://www.excelguru.ca/</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-decoration: underline;color: #0000FF;;">Atlas PM</td><td style="background-color: #FFFF00;;">http://www.atlaspm.com/</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-decoration: underline;color: #0000FF;;">DataWright</td><td style="background-color: #FFFF00;;">http://www.datawright.com.au/</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style="text-decoration: underline;color: #0000FF;;">ColinLegg</td><td style="background-color: #FFFF00;;">http://www.colinlegg.co.uk/</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style="text-decoration: underline;color: #0000FF;;">Excel Design Solutions</td><td style="background-color: #FFFF00;;">http://www.exceldesignsolutions.com/</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">6</td><td style="text-decoration: underline;color: #0000FF;;">MrExcel</td><td style="background-color: #FFFF00;;">http://www.mrexcel.com/</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=GetHyperlink(<font color="Blue">B2</font>)</td></tr></tbody></table></td></tr></table><br />

Public Function GetHyperlink(ByVal rngCell As Range) As Variant
    Dim hyp As Hyperlink
    
    If rngCell.Count > 1 Then
        GetHyperlink = CVErr(xlErrValue)
    Else
        On Error Resume Next
            Set hyp = rngCell.Hyperlinks(1)
        On Error GoTo 0
        If hyp Is Nothing Then
            GetHyperlink = CVErr(xlErrNull)
        Else
            GetHyperlink = rngCell.Hyperlinks(1).Address
        End If
    End If
End Function

Now say you are looking up index #2 in the table above (Atlas PM). A conventional VLOOKUP will return Atlas PM. To hyperlink it you would use:

=HYPERLINK(VLOOKUP(2,A2:C7,3,0),VLOOKUP(2,A2:B7,2,0))

 

[bdumaguina]

Thank you very much! I will give these suggestions a try.