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]

That worked!

Great! Thanks for letting us know - come back anytime!

 

[cmurray2]

Thanks FormR... I think that I may be pushing my luck here, but I am coming back with a query related to this.

Have tried tinkering about with the Macro after reading a few threads on 'IF' functions, and I came up with this (have tried a few variations with no luck):

Function GetHyperLink(r As Range) As String
'If cell does not contain a hyperlink, return default_value
If r.Hyperlinks.Count <> 1 Then
GetHyperLink = default_value
Else
GetHyperLink = r.Hyperlinks(1).Address
End If
End Function


I am not sure if I should be saying "default_value" as i've just picked this up from elsewhere.

The problem is that every cell with the Hyperlink Vlookup Formula in it (i.e every cell on Sheet 2) is returning text which is blue and underlined (so, formatted like a hyperlink) even although only some of them should be hyperlinks as per the corresponding cell in Sheet 1. Hope that makes sense.

So, if there are no hyperlink in the cell which is being 'lookedup' in Sheet 1, I would like the formula to return just the text; if there is a hyperlink, would like it to return the hyperlink along with the friendly name (which you have cracked and is working fine!).

Hope you can help again!

 

[FormR]

The problem is that every cell with the Hyperlink Vlookup Formula in it (i.e every cell on Sheet 2) is returning text which is blue and underlined

Hi, I think it may be a case of Excel being a little too clever for it's own good, from some limited testing it appears that any cell containing the HYPERLINK() function defaults to that style.

You may need a pure VBA solution to workaround this - which is not something I'm good at! If this is important to you post back with a "Bump" and hopefully someone else will jump in.

Excel Workbook
	A	B
1	0	I'm a VALid hyperlink
2	1	I'm NOT a hyperlink!
Sheet1

 

[cmurray2]

Thanks FormR,

I am not sure how to 'bump' so I think I will start a new thread with where we are at! So close!

 

[cmurray2]

Is there anyone that can help with a VBA work-around?

Thanks!

 

[cmurray2]

FormR! So sorry to bother you again, but I am trying to copy the formula (or fill it) horizontally accross the sheet and I just don't know why this will not work!!

I have moved the above formula from column B (which you kindly provided able) along to column C and have been messing about with it for days with no joy. This is where I am at, at the moment:

=HYPERLINK(GetHyperLink(INDEX('Internal Tracker'!C$1:C$10001,MATCH(A4,'Internal Tracker'!C$1:C$10001,0))),(VLOOKUP(A4,'Internal Tracker'!A$1:C$10001,3,FALSE)))

And I just can't understant why it won't work! Would you have any advice? Column A on internal tracker contains the lookup and column C contains the hyperlink that I want to return.

 

[FormR]

Hi cmurray,

Try the following in cell B4 copied down and across..

=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,COLUMNS($B:B)+1,FALSE)))

BTW, were you able to get around the formatting problem?

 

[cmurray2]

Hi! Thanks for your assistnace again FormR... the formula is pulling accross now with (mostly) the correct info being displayed in columns C and D; there seems to be one little glitch though - some random cells in col's B and C are instead of showing the lookedup value, are showing an error message "#value" - there appears to be a trend in that the cells which display this error message should be pulling through a faily large amount of text (i.e. about 100 words); since it is an internal log, some text in cells can get lengthy. I wasn't getting this glitch with just a normal VLOOKUP so I am not sure why it is showing now! (re-sizing the cells doesn't help). Any ideas why this might be happening? :S

The formatting problem; I am getting there! I have been offered a few non-VBA solutions:
1. conditional formatting (this is getting messy and not quite working yet!)
2. Changing Excel's 'behaviour' by going into Excel Options>Proofing>Autocorrect Options> and 'unticking' the 'Replace as you Type' 'Internet and network paths with hyperlinks'. This seems the best option so far, but not overly user-friendly as it automatically defaults to black and not underlined and if I want it to show as a hyperlink (blue and underlined) then I put the cursor at the end of the formula and press Enter, so need to do every cell individually which should show as a hyperlink. This is fine for me to maintain, but there will be quite a few people using this so I am concerned about it being confusing- it should just be a simple work-tracker! But, I will go with this option for a short-term solution until and unless I can find a better one!

 

[FormR]

the cells which display this error message should be pulling through a faily large amount of text (i.e. about 100 words)

Hi CM2 - It's getting somewhat complicated and expensive in terms of calculation time, but try in B4 copied down and across:

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

 

[cmurray2]

Thanks FormR; I know, I had hoped that it would just be a simple solution had no idea it would get so compicated at every turn! Thank you for your time. This didn't work unfortunately, the same cells are still showing as "#VALUE!".