Vlookup a Hyperlink on another Sheet that is to a redirected Website?

[gmooney]

I would like to have cell A1 in DATA do a vlookup over to ECRMDATA and find and recognize a hyperlinked cell that goes to a redirected website....

Every time I try to follow all suggestions I get the "cannot open the specified file" error but if I go to ECRMDATA sheet and click on the hyperlink it takes me to the redirected website correctly.

Here is my formula in cell A1 of the DATA sheet.

=HYPERLINK(VLOOKUP(DATA!$AO$3&'AD CODING'!$B3&'AD CODING'!C3,ECRMDATA!$A$2:$AW$8500,5,0))

DATA cell A! result is :Front (1) which is what is the vlookup result from column % of the ECRMDATA sheet has the hyperlink that will launch successfully to the redirected website.

Here is the URL that is hyperlinked into the ECRMDATA sheet cell that I am trying to lookup:

ECRM® Data

Any help would be greatly appreciated.

 

[gmooney]

The AD Details is how I get this data from another source that I paste into my Excel file so I don't think I have the ability to show the real URL versus the user friendly name? If not can you help with the UDF or do you know if I copy this data from one Excel file and paste it into this file can I get it to paste the real URL?

 

[Jerry Sullivan]

You could use this macro to have any selected cells to display their hyperlink addresses.

Sub DisplayHyperlinkAddress()
 Dim hyp As Hyperlink
 
 For Each hyp In Selection.Hyperlinks
   hyp.TextToDisplay = hyp.Address
 Next hyp
 
End Sub

 

[gmooney]

I ended up using the following UDF called Hlink and it is getting me closer.....

Function HLink(rng As Range) As String
If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
End Function

I am now able to have a cell that displays the URL completely but I want that same cell now to be able to be clicked on and go to that URL? Is that possible?

 

[Jerry Sullivan]

The macro approach I suggested in post #12 is simpler. It provides what you are wanting without the use of a UDF (that requires a macro-enabled workbook) or a helper column.

 

[gmooney]

Okay so I can switch to that but not sure what I need to do with it....

 

[Jerry Sullivan]

Try this on a copy of your workbook...

1. Paste the code into a Standard Code Module (the same kind as you used for your UDF).

2. Select the range of cells in Column E that have hyperlinks.

3. Key Alt-F8 to bring up the Macro Dialog.

4. Select DisplayHyperlinkAddress from the list of Macros then click OK.

All the hyperlinks selected in Col E should now have their URLs displayed instead of "Friendly Names"

 

[gmooney]

Thank you Jerry...almost there.....your UDF worked and I can now see the URL name as a clickable hyperlink. The last thing I need to be able to do is click on a cell from another sheet that does a vlookup to one of the values in my column E. How can I do this?
I have about 8,000 hyperlinks in this file.

Thanks, Greg

 

[Jerry Sullivan]

First confirm that an indirect HYPERLINK formula will also work when referencing that cell.

On the same worksheet as your 8,000 hyperlinks, enter this formula and reference one of those hyperlink cells instead of E1.

=HYPERLINK(E1,"Click me")

If that HYPERLINK formula also works, your last step is to make a VLOOKUP formula that will find and return that URL string, and replace E1 with that formula.

Your previous Vlookup was this formula.
=VLOOKUP(DATA!$AO$3&'AD CODING'!$B3&'AD CODING'!C3,ECRMDATA!$A$2:$AW$8500,5,0))
It builds a lookup value joining values from 3 cells. I'm not able to test if that works without knowing the values in your workbook.

Get your lookup formula to return the URL on its own before moving on.

If that works, your last step is to swap it into the HYPERLINK formula...
=HYPERLINK(VLOOKUP(DATA!$AO$3&'AD CODING'!$B3&'AD CODING'!C3,ECRMDATA!$A$2:$AW$8500,5,0))
,"Your friendly text")

 

[gmooney]

As I am trying to run your macro on my 8000 rows it is staying on calculating 2% to 3% then back to 2% then 3%???Is this because it is having to go to each URL? This may be very slow......luckily I only have to do this once a month.

 

[gmooney]

As I am trying to run your macro on my 8000 rows it is staying on calculating 2% to 3% then back to 2% then 3%???Is this because it is having to go to each URL? This may be very slow......luckily I only have to do this once a month.

It's weird because I hit escape and debug when it is on the Next hyp line. I stop the code from running and most of the 8000 rows still show "AD Details" as opposed to the URL....But you can go to the other tab and click on the vlookup formula for one of those and it still pulls up the correct webpage?