Hyperlink usage and HyperlinkOf UDF

The Hyperlink function is really powerful and yet not widely used.

You can create a whole navigation system with Hyperlink fx or create a custom jump to link to take user to certain area.

Of course the “Jump-to” link need other functions, like Match, Indirect and Index

Here is a sample of what we can do with Hyperlink and other functions

It also has the UDF (User-defined-function) that called HyperlinkOf to get the actual Hyperlink address of a cell hold a regular hyperlink (created with Insert –> Hyperlink)

This one has a lot of formulas to make it work well:

First: we have the formula that brings the path where this Excel file is saved:


Then: we have the one that grabs This Excel file name:


Then: The one that brings Active sheet name:


And off course: the one that Constructs the Hyperlink itself


And as a bonus, the UDF that reads the URL from a flat Hyperlink: