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.

 

[Jerry Sullivan]

I mocked up a workbook with 8000 different hyperlinks to that website and it only took about 5 seconds for the code to convert the hyperlinks to display their URLs.

If you have a lot of formulas in your workbook that are referencing that range of cells, they could be recalculating each time one of hyperlinks is modified.

First try doing a test in a blank new workbook with no other workbooks open. Paste your 8000 hyperlinks as you would once per month. Paste the macro in a Standard Module. Then run the macro.
If it runs much quicker than before, the problem could be with recalculating your formulas.

If that's the case, you could modify the macro to temporarily set Calculation to Manual.

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