HYPERLINK forumla with hyperlink base

Hi Everyone,

I have an Excel 2010 file with a lot of external links to PDF files stored on a server. I used a VB macro to read all the files and create their hyperlinks. However, only after I spent hours getting the macro refined the way I want it, i realized it was inserting relative hyperlinks. That was not a problem until I started emailing the file to people and all the links broke... surprise, surprise.

So, a simple fix i added the missing absolute piece of the file location into the Hyperlink Base for the workbook. Everything seemed fixed. I can send the file around at all the links are correct so long as you remain on the LAN.

Now, here's my problem... I also have links in the workbook to other Sheets. Adding the Hyperlink Base now is causing the internal links to fail. It seems I cannot have my cake and eat it too! How can I get these cells to "ignore" the hyperlink base and just know that im referring to another cell within the workbook which is currently open.

For additional information, my external LAN files are located at \\server\folder1\files. I added \\server\folder1 as the hyperlink base. But I also want to link to "Sheet2" not "file:///\\server\folder1\Sheet2"... which, of course, does not exist.

I tried using the hyperlink tool to select the sheet AND i tried the =HYPERLINK() function with no luck.

I'm stumped (and frustrated!). Any thoughts? Thank you!

Hi qwicker,

You can override the Hyperlink Base if you include the workbook's file path.

Since hard coding the path in the Hyperlink address is problematic if you are going to move the file to another folder, you can make that dynamic with one of these methods...

Define a Named Range with Workbook scope to reference the workbook's current file path:
Name: ThisWorkbookPath
Refers to: =SUBSTITUTE(LEFT(CELL("filename"),SEARCH("]",CELL("filename"))-1),"[","")

Then you use the HYPERLINK() function for hyperlinks within the same workbook like this...
=HYPERLINK(ThisWorkbookPath& "#Sheet2!D3","MyDisplayText")

Another option would be to use a UDF (User Defined Function) to return the string needed by the HYPERLINK() function.

Place this code in a Standard Code Module in your workbook with the hyperlinks...

Please Login or Register  to view this content.

Then you could use this syntax:
=HYPERLINK(ThisWorkbookRef(Sheet2!D3),"MyDisplayText")

The UDF method has the benefit of having a direct cell reference that will update in the event the sheet is renamed or the referenced cell is moved. That could be accomplished with the the formula method, but it the formula would be lengthy.

Hi JS411,

Thank you for your help. This looks like the right approach. I tried the UDF version as I too prefer that. I inserted it (just as you wrote it) into a new module in my workbook. I then included the formula in the cell where i want the link. However, when I click on the cell nothing happens. It's like the function is not working.

Forgetting about the hyperlink for a moment, I tried to test the UDF by typing =ThisWorkbookRef(SheetName!Cell) and it doesn't return anything... but as I type the function "ThisWorkbookRef" pops up indicating that Excel has recognized the UDF in the module I inserted it into.

Any thoughts? I appreciate your help!!!

Haha literally after I posted this, I looked at the code again. I think the problem was with the "ThisWB" inside the function. I changed the name to "ThisWorkbookRef" and now it works!

Thank you

Oops. I renamed that during my posting to make it clearer. I'm glad you were able to spot and fix the error.

Cheers!

Hi Again,

So I thought I had this working, but I saved and closed the file and opened it a few days later now and it does not work again! Frustrating.

The function does not seem to be returning the right string in the hyperlink function. All my cells say "#NAME?" now.

The function is just as you had it with the variable name corrected as I indicated above:

Please Login or Register  to view this content.

Any thoughts?

Do you have the UDF code in a Standard Code Module (not a sheet code module or the ThisWorkbook module) in the same workbook as the formulas?

If you start a new formula in a blank cell with "=ThisWo" does the formula box show a dropdown with the function ThisWorkbookRef?

If the function is available, but the not recalculating, you could try adding Application.Volatile to the code; however in my test this wasn't required for the formula to keep its value upon closing and reopening.

Please Login or Register  to view this content.

So I think my problem was with my Macro security settings. I changed them and now the code is working. I have the UDF in a standard module (not in the worksheet) and the function was always popping up when I started to type it. It just would not actually execute the function due to my macro settings. It seems OK now and I've saved the file and moved it to different computers and locations and it all works.

Thank you so much for your help. This was a great learning exercise for me too!