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]

Do the hyperlinks you are looking up have "Text to Display" that is different from the "Address"?

 

[cmurray2]

Hi FormR,

Yes (I think I understand your Question) - so in Worksheet 1, the cell I want to be brought through to Worksheet 2, reads (for example) 'Housing Bill' and the words themselves I have hyperlinked to a website. I cannot separate the words and the URL into different columns as this really wouldn't suit the type of ssheet I am trying to create, and it is more than one column that I have hyperlinks in so would get very messy!

Thanks!

 

[FormR]

Welcome to the Forum btw!

Give this a try:

Put 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

And use the formula as below:

Excel Workbook
	A	B	C
1	Lookup	Hyperlink	With Friendly Name
2	A	http://google.co.uk/	GOOGLE
3	C	http://bing.com/	bing
Sheet2

Excel Workbook
	A	B
1	Lookup	Entered Hyperlink
2	A	GOOGLE
3	B	Amazon
4	C	bing
Sheet1

 

[FormR]

Example here:

https://www.dropbox.com/s/th5pyq4exqejbv1/HyperlinkAddressExample.xlsm

 

[cmurray2]

Thanks! I will try to help out others but am no expert, clearly! and Thanks for getting back so quick FormR.

Sorry to be a pain but this does not appear to be working. I have checked the Macro and formula's and I do seem to be following what you kindly show here.

I have tried both formula's for B2 and C2 as per your example (although for my purposes I am only looking for the result as per C2 - with friendly name) and just getting the "#VALUE!" response in the cell.

I don't think that I can see the drop-box example properly - I have some restricted access issues on my work computer unfortunately!

I am looking for a way to screenshot to you what I am doing but even that is eluding me!!

Sheet1 is called: Internal Tracker, Sheet 2 is called: External Tracker, and I am using the following fomula for friendly name:

=HYPERLINK(GetHyperLink(INDEX('Internal Tracker'!$1:$65536,MATCH(A4,'Internal Tracker'!$1:$65536))),(VLOOKUP(A4,'Internal Tracker'!$1:$65536,2,FALSE)))

A4 is the look-up cell (equivalent to A2 in your example above).

I am unfamiliar with Macros, but I have just pasted the formula you had provided into Module 1 and pressed save and went back to the SSheet which I think is the correct way to do this.

Is there anything that you can see I am doing inconsistent with your example?

 

[FormR]

Hi,

What column on the "Internal Tracker" sheet contains the lookup value you are matching to cell A4 and which column contains the hyperlink that you want to return?

 

[cmurray2]

FormR... please see below;

What column on the "Internal Tracker" sheet contains the lookup value you are matching to cell A4 : Column A

Which column contains the hyperlink that you want to return: Column B

Thanks

 

[FormR]

OK give this a go:

=HYPERLINK(GetHyperLink(INDEX('Internal Tracker'!$B$1:$B$10000,MATCH(A4,'Internal Tracker'!$A$1:$A$10000,0))),(VLOOKUP(A4,'Internal Tracker'!$A$1:$B$10000,2,FALSE)))

 

[cmurray2]

FormR!!!!! That worked! How wonderful, cannot tell you how happy that makes me (I don't care how sad that sounds haha!).

Wish I could buy you a pint or similar but good tidings your way from me, your assistance is greatly appreciated.

I am copying the formula down and accross and all the text is showing blue and underlined (evan although not every cell has a hyperlink) so I am going to tinker about with it - probably some 'If' formula will help - but if I get stuck I can hopefully ask you for further advice!!