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")