INTHEBLACK October 2021 - Magazine - Page 72
WORK SMART
// E XC E L YO U R S E L F
STORY NEALE BLACKWOOD CPA
HYPERLINKED
INDEX SHEETS
HYPERLINKED INDEX SHEETS ARE USEFUL TOOLS FOR SPEEDING
UP AND SIMPLIFYING NAVIGATION AROUND LARGE EXCEL FILES.
I
t is best practice to use separate sheets for
separate sections of your Excel file. This
means you should have separate sheets for
instructions, inputs, data, reports, dashboards,
validations and workings. This can lead to a
lot of sheets, and the more sheets you have,
the harder it is to navigate between them all.
In these situations, it is useful to have
an index sheet, with hyperlinks to all other
sheets, to speed up and simplify navigation
around the file. On each sheet, you can also
have a hyperlink back to the index sheet.
Creating a hyperlink is a manual process
that takes a few clicks, and manual hyperlinks
can break if the sheet name changes.
I will share a way to create a flexible index
sheet, starting with non-hyperlink solutions.
01
LINK HACK
Creating a formula that links to another
sheet is quick and easy. Combine that with
a keyboard shortcut, and you have a robust
alternative to hyperlinks.
With a cell selected that links to another
sheet, or another cell in the current sheet,
you can press Ctrl + [ to jump to that cell. To
return, you press the F5 function key, then
press Enter. This return shortcut also works for
the hyperlinks that follow. These shortcuts are
also useful when reviewing formulas.
MOUSE HACK
You can right-click the navigation arrows on
the left on the sheet tabs at the bottom, left
of the Excel screen, as shown in Figure 01.
02
Neale Blackwood CPA runs A4 Accounting,
providing Excel training, webinars and consulting
services. Questions can be sent to a4@iinet.net.au
When you right-click, a list of all the sheets
is displayed, as seen in Figure 02.
In older versions of Excel, the list may only
show the first 10 sheets.
FORMULA-BASED HYPERLINKS
You can create flexible hyperlinks using inputs
and the HYPERLINK function. This function can
be applied elsewhere to include hyperlinks with
instructions and standard procedures.
The HYPERLINK function is not easy to use,
but it can be simplified with a trick. Figure 03
has a table we will use to create an index sheet
for a file.
Columns A and B have data entries. If a
sheet name changes, you need to update
the name in column A. Changing either the
sheet name or the cell reference adjusts the
hyperlink destination in column C. As you
can see from row 12, you can even create a
hyperlink to a range. Column C contains the
HYPERLINK function.
The formula in cell C4, which has been
copied down, is:
=HYPERLINK("#'"&G4&"'!"&B4,"Link")
The # symbol at the front is the trick to
making this work. This symbol makes the
sheet or cell reference easier to create.
The HYPERLINK function in cell C4 requires
the following text string in its first argument
to create the hyperlink:
#'Instructions'!A10
The single inverted commas must enclose
any sheet name that includes a space.
The text in the second argument of the
HYPERLINK function has the text that
is displayed in the cell. The text will be
automatically underlined like a normal
hyperlink.
Being able to specify the sheet name and
the cell or range reference gives you the
flexibility to link to anywhere in the file.
If you specify a hidden sheet name, nothing
happens when you click the hyperlink. If the
sheet name is wrong, then the error message
in Figure 04 displays.
SHEET NAME CHANGES
You can extract a sheet name using a formula.
Instead of typing the name in the index sheet,
you have a couple of options.
You could create a formula in the
destination sheet that extracts the sheet
72 ITB October 2021