Preserve hyperlink when using VLOOKUP (to a web address) - no solutions?

[cmurray2]

Hello all,

I am having a problem when using the VLOOKUP function. I have two worksheets within the same workbook. Worksheet 1 contains a lot of information for internal use(many of the cells in all of the columns contain hyperlinks to web addresses), and Worksheet 2 should be a version identical to this, showing only the selected columns suitable for external use. This is to avoid using two different 'work trackers'; so, when info in Worksheet 1 is updated, Worksheet 2 should automatically be updated and reflect this.

The only way I can think to do this is VLOOKUP. I have managed this somewhat successfully using the basic
=VLOOKUP(A5,'Worksheet 1'!$1:$65536,2,FALSE)

However, it is only returning the Value (e.g text) from Worksheet 1 and none of the hyperlinks. I have searched the internet and forums and there have been many threads on this which do not quite answer my problem. Although, I have tried two options which nearly get me there!

First, I have tried

=HYPERLINK(VLOOKUP(A4,'Worksheet 1'!$1:$65536,2,FALSE))

This looks perfect, but when I click on the hyperlink/cell in Worksheet 2, I get the error message "Cannot open the specificed File". Options for a workaround on these forums have not suited my example, as, it seems that the Hyperlink function requires me to have the text (value) and hyperlink (web address) in separate columns. This is not suitable, as I have hyperlinks in pretty much every column on Worksheet 1. I also cannot create a separate sheet with always updating web addresses; i am trying to make this as user friendly as possible for the team so that they can easily update info on our internal sheet and be confident that the external sheet is accurate at all times.

Second, I have tried a Macro (first time ever!), but this only pastes the actual URL address into the required cell in Worksheet 2.

I hope I have explained that clearly. Does anyone have a way to make this work with VLOOKUP, or have an alternative idea to VLOOKUP??

Thank you in advance!

 

[FormR]

This didn't work unfortunately, the same cells are still showing as "#VALUE!".

Hi - it seems to work for me; maybe your lookup value doesn't exactly match the text in the "Internal Tracker" sheet. But even if you get this working I think you will have a noticeable calculation lag!

Excel Workbook
	A	B	C
4	Small Text	amazon	bbc
5	Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text	google	bing
Sheet1

Excel Workbook
	A	B	C
1	Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text Large Text	google	bing
2	Small Text	amazon	bbc
Internal Tracker

 

[cmurray2]

Hi FormR,

I have looked into it and there seems to be a 255 character limit on both vlookup and MATCH functions! When I look at the data which should be pulling through to the external sheet but does not and is showing the #VALUE! error, this is all >255 characters, and the data which does pull through is <255 characters, so this must be the problem! Just don't understand how you managed to circumvent it with your example?

Having looked at some threads on the issue, I need to enter some sort of VBA solution but I am not sure how to integrate that into the Macro/Code I am currently using?

 

[FormR]

Hi,

Did you try the formula in post #21 - it is designed to overcome the 255 char limitation by creating an array of Boolean values to match against instead of the text itself.

 

[FormR]

Hi cmurray,

OK - the problem wasn't the lookup value in MATCH() function being greater than 255 chars, it was rather the text being being returned and passed to the HYPERLINK() function was greater than 255 chars.

I can't find a good workaround for this, and at this stage I think we have probably exhausted all the formula based options!

I've had a go at writing some VBA - please note that I am no expert in this area and there will be better ways and you should test (thoroughly) on a backup of your spreadsheet.

But with that caveat, give this a try:

Place all of this code in a standard code module:

Function GetHyperLink(r As Range) As String
If r.Hyperlinks.Count Then
GetHyperLink = r.Hyperlinks(1).Address
End If
End Function

Sub ApplyLinks(rLookupValue As Range)

Dim rLookupTable As Range
Dim i As Long, j As Long, k As Long
Dim lr As Long
Dim found As Boolean

lr = Sheets("Secretariat Tracker").Range("A" & Rows.Count).End(xlUp).Row

Set rLookupTable = Sheets("Secretariat Tracker").Range("A5:A" & lr)

For i = 1 To rLookupValue.Rows.Count
    For j = 1 To rLookupTable.Rows.Count
        found = False
        If rLookupValue.Cells(i, 1).Value = rLookupTable.Cells(j, 1) Then
            found = True
            For k = 1 To 3
            rLookupValue.Cells(i, 1).Offset(, k).Value = rLookupTable.Cells(j, 1).Offset(, k).Value
            If rLookupTable.Cells(j, 1).Offset(, k).Hyperlinks.Count Then
                rLookupValue.Cells(i, 1).Offset(, k).Hyperlinks.Add rLookupValue.Cells(i, 1).Offset(, k), _
                GetHyperLink(rLookupTable.Cells(j, 1).Offset(, k))
                    Else
                rLookupValue.Cells(i, 1).Offset(, k).Hyperlinks.Delete
            End If
            Next k
            Exit For
        End If
    Next j
    If found = False Then
        For k = 1 To 3
            rLookupValue.Cells(i, 1).Offset(, k).Value = ""
            rLookupValue.Cells(i, 1).Offset(, k).Hyperlinks.Delete
        Next k
    End If
Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

And place this code in the "council tracker" sheets code module

Private Sub Worksheet_Activate()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Call ApplyLinks(Range("A4:A" & lr))
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
If Not Intersect(Target, Range("A4").Resize(UsedRange.Rows.Count)) Is Nothing Then
    For Each c In Intersect(Target, Range("A4").Resize(UsedRange.Rows.Count)).Cells
        Call ApplyLinks(c)
    Next c
End If
Application.EnableEvents = True
End Sub

I have also sent you the example file back.