Excel model can be as simple as adding up two values in cell or so complicated to cover multiple sheets or even multiple workbooks. Ideally, as suggests experts, the excel model should be spilt into parts and named so that its liability to error can be reduced and error detection process becomes easier.

Just in context of such large and complicated model, it is often desirable to make a table of content sheet or a sheet we can use to navigate through the model. Such  a sheet contain links to all of the sections of a model, usually sheets, that makes shuffling through them easier and makes your model look more professional, increases traceability of information and robust against errors.

Most of the time, we use our pointing devices, mouse and/or keyboard to navigate through a sheet. But today we will learn a trick that will allow us to move from one point to another one using “hyperlinks”

Wikipedia describes the one as “a link from a hypertext file or document to another location or file, typically activated by clicking on a highlighted word or image on the screen.” and we are going to do the same here.

FORMULAS WE WILL USE:

We will use two formulas …

1. VLOOKUP()

2. HYPERLINK()

The VLOOKUP () formula:

The syntax of VLOOKUP() formula has been described in number of our posts but just to revive what it does, here is a simple explanation:

The HYPERLINK () Formula:

The HYPERLINK () formula takes two argument, first is a reference to a point, and the second is masking text, which is optional. The syntax of the function is:

The Example: Our example workbook consists of Balance sheets for the year 2013, 2015 and 2016 and we interested in pointing or reaching to different areas of the balance sheet.

The helper sheet is setup to list all the possible options on the drop down in the main selection sheet with cell references just in front of it. We could have set static hyperlinks (by going to Insert>Hyperlink) but that would have been static - and we have used here drop down.

The main sheet consists of a drop down and a cell with dynamic hyperlink reference. Upon the selection from the menu, the hyperlink reference changes automatically.

The Balance Sheets are the sheets various area of which we want to navigate to.

The formula that performs the trick is given below. The various parts of the formula are explained in the following diagram as well. The formula starts by executing for the inner VLOOKUP that fetches the corresponding link from the table in helper sheet. The link is feed to the first argument of HYPERLINK formula and we have VIEW as masking text. when clicked, it takes up the referred cell.

Thus with a little tweaking of formula we have a more professional and more organized work sheets.