Create Conditional Hyperlinks in Excel

Take your hyperlinks to another level with the HYPERLINK function. This function can be used to create conditional hyperlinks that only display when required. The hyperlinks are also dynamic and can change their link location or anchor text automatically.

In this blog post we will look at both of these techniques.

Create Conditional Hyperlinks

In column E below a hyperlink has been used to link to the PO. This link is automatically generated when somebody states that a PO has been received by selecting yes in column D.

The following formula has been used in column E.

=IF(D2="Yes",HYPERLINK(CONCATENATE("G:\Purchase Orders\",C2,".pdf"),"View PO"),"")

An IF function has been used to check if a PO has been received or not and runs the HYPERLINK function if the value is true.

The HYPERLINK function concatenates a link location together using a static file path and a reference to column C. In this example all PO’s are saved using the booking ref as a filename.

The blue and underline text formatting has been done manually.

Create a Table of Contents that uses Dynamic Hyperlinks

In large workbooks a table of contents can be created to provide an easy way to jump to the required sheet.

Note: Check out this blog post on using a macro to automatically create table of contents in Excel.

In the image below, the following HYPERLINK function has been used in cell C3. This function uses the text from cell B3 to build the link location.

=HYPERLINK(CONCATENATE("#",B3,"!A1"),"Go to page")

Watch the Video on Creating Dynamic Hyperlinks