Creating hyperlinked index sheets

Intro:
Hello and welcome to the CPA Australia Podcast, your weekly source for accounting, education, career and leadership discussion.

Neale Blackwood CPA:
In the October 2021 article, I've covered how you can create an index sheet for your files. Now, it's pretty much best practise to split up your files into separate sheets for different processes or tasks. So you might have an input sheet, you might have a data sheet, you might have an actual sheet, you might have a budget sheet and all different types from validations, workings, instructions. And so that ends up with quite a few sheets. So it's a good idea to the very first sheet to have an index sheet. Now, creating an index is usually done with hyperlinks. Now, hyperlinks are good, but the problem is they can be easily broken if a sheet name changes. They're also like a manual process and you can't really copy them. So if you create a hyperlink to the instruction sheet, then it's not easy to actually copy that and then amend it to say link to the validation sheet, whereas you can use links in Excel to pretty much do the same thing. And links are easy to edit and to change whereas hyperlinks aren't easy to edit.

Neale Blackwood CPA:
They are editable, but you have to right click and edit and change it and then click, "Okay," and all that sort of stuff. It's a lot simpler to change a link. So there's a couple of hacks you can do that actually avoid hyperlinks. So one of them is you can use the link to your advantage. So what you can do is link to a sheet and now, you have to train the people to use this. And if you're just using it yourself, you just need to know the shortcut. There is a shortcut that will follow a link and it's Ctrl and the left square bracket. So you basically select a cell that has a link in it and you hit Ctrl and the left square bracket and that will take you to the link in the other sheet, so you can navigate to the other sheet. To go back, which is a typical thing that you need to do, go back to where you were, there's another keyboard shortcut. So you press the, "F5," function key and then you press, "Enter." So just press, "F5," and then press, "Enter."

Neale Blackwood CPA:
If you're on a laptop, you might need an extra key to actually select your function keys because sometimes, laptop have limited keys on them. Okay, so that's one hack. You can actually use a link to navigate around a file. So you can actually just do links to all of the other sheets in the index sheet and then follow it using that keyboard shortcut. There is another shortcut you can use using the mouse. Now, on the sheet tab, so you have the sheet tabs down the bottom of the screen, on the left hand side of the first sheet are a series of little arrows. Now, you can right click that little section and it will give you a list of all of the sheets. And you can go to the sheets by selecting the name and clicking, "Okay." So it actually gives you a list of all the sheets in the file. And there is another hack that I didn't include in the article. Unfortunately, there is space limitations there. This one involves a setting and it's a combination of the mouse and the links side of it.

Neale Blackwood CPA:
So what you can do is go click on the file ribbon tab and then click on, "Options," on the left hand side and then select, "Advanced." And there's an option to edit directly in cells and that's ticked, that's the default setting for it. If you untick that, what it means is you can't edit in the cell, but when you double click a cell, you will follow the link. So normally, when you double click when you're editing, you actually start editing that formula. But when you turn this option off, when you double click, it'll follow any links that are in the cell. So you need to click, "Okay," to accept it after you've unticked it, and then you can just double click any link and that will take you to wherever the link is. Now, this is useful for other things apart from navigating. You might be trying to track down where some of the values are coming from. And so you can use this technique to do that as well. Now, the formula based hyperlink.

Neale Blackwood CPA:
So the way that the example file is set up is that cell A1 in all of the sheets has a special formula in it. And that formula, it's a single formula that extracts the sheet name. Now, I have covered that formula in a previous article when I did the let function. So there is a formula, I've included both formulas. I won't go through them in the podcast because they're a bit long. But basically, the first one can be used in any version of Excel and it starts with the right function. And then the other function is the let function which is only available in the subscription version of Excel currently. Now, that's a lot shorter. That's the advantage of the let version. It's the example I used in the article a few months back to demonstrate the let function. And so you can have a single formula and it's in A1 in every single sheet. And what we do on the index sheet, we link to sell A1 in all of the other sheets. So we have a list of all of the sheet names.

Neale Blackwood CPA:
The beauty with that is it automatically updates when the sheet name changes, which is important. So you don't have to maintain the sheet names. You could just type them in, but when you do that, you have to maintain them if the sheet names do change. Okay, so we've got a table. We've got the sheet name in column A and we've got a cell reference in column B. And in column C, we've got a hyperlink function. So there is a hyperlink function. Basically, it has two parts. The first part is the reference that you want to link to and then the second part is just the text that you want to display in the file, which I've just used link. You need to put quotation marks around it. Now, in this hyperlink that I used, I've used the hash symbol at the front, which is like a bookmark symbol in this case. And then you build the reference using the ampersand symbol. The ampersand symbol joins text together. You might have used the concatenate function. It does pretty much the same thing.

Neale Blackwood CPA:
And so what you do is you have to mimic whatever the normal cell reference is. Now, the secret here is the apostrophe. Unfortunately in Excel, if you link to a sheet that doesn't have a space in the sheet name, the reference is slightly different to if you link to a cell in a sheet that does have a space in the sheet name. And the difference are apostrophes. So there's an apostrophe on either side of any sheet name that has a space in it. Now, because of that, we need to build up this cell reference in incorporating the apostrophes basically. So even if the sheet doesn't have a space, we're still going to put apostrophes around it. And so basically, you build up that reference. That is what is then used by the hyperlink to then go somewhere. So it's dynamic. So you can change, you can copy the formula down, you create one formula, copy it down. And then that will work on all of the different sheets that you've got listed. And the very last example in the table is a range. So you can even link to a range in another sheet. Neale Blackwood CPA:
So maybe it's an input range, maybe it's some other range that you need to amend. So you can do a link there. And as I said, these techniques can be used for other things. Apart from the index, you can use them for things like instructions or documentation as well. I recommend that on all of the other sheets that you have a link back to your index as well. Now, you can just do it with a simple hyperlink, or you could link it to an image. So in the article, I've used a boomerang image because you want to go back. And so you can right click the image and there's a link option when you right click, and you can create the hyperlink using that. And once you've created that image, you can just paste that image on the other sheets so that each of those sheets has a consistent way to get back to the index. Finally, I finished off with a little technique that allows you to enter the same formula in every single cell in a series of sheets.

Neale Blackwood CPA:
So for example, our A1 formula that has the name in it, we could just do that once basically. Now, this technique does come with a warning because what you're going to do is you're going to group the sheets together. And when you do that, whatever you're doing in one sheet is actually replicated in every other sheet that you've got selected, which is really powerful if they're all laid out the same, but it's really dangerous if they're not. So you've got to make sure that what you're doing is consistent across all of the sheets. And basically, you select the first sheet, hold the, "Shift," key down and click on the last sheet that you want to edit. All of them will be highlighted and the word, "Group," will appear in the top of your screen where the name is. That's the only difference in terms of Excel warning you that you're actually in multiple sheets. So you can create the formula in A1 and hit, "Enter," and that will be placed in A1 in all of the sheets that you've got selected.

Neale Blackwood CPA:
Then it's vital that you click another sheet or you right click and say, "Ungroup," you're no longer editing all of those sheets. It is dangerous. If you are grouping and someone comes in the room and interrupts you, don't let them. Just ungroup the sheets first because you don't want to leave them grouped. So it's a really powerful technique, but also, you've got to be really careful with it. So always make sure you ungroup after you've grouped. There was something else that I shared in the video as well. And it's the fact that you can unhide more than one sheet at once. In the example, if you hide a sheet and you use the hyperlink function, you don't get an error if that sheet is hidden or anything, it just doesn't do anything when you click the link. But when I was demonstrating it, I showed that you can now, so in the latest version of Excel, when you right click and unhide, you can now unhide multiple sheets.

Neale Blackwood CPA:
In the old days, you could only unhide one sheet at a time. And it's been a request for a very, very long time to actually be able to unhide more than one sheet and now you can do it. So check out if you can do it in your version, so right click, unhide and you should be able to use the Shift key or the Ctrl key to select more than one sheet to unhide. Okay, so there's some ideas for creating hyperlinks for indexes and for other reasons throughout your file. I hope you found that useful. Thanks for listening.

Outro:
Thanks for listening to the CPA Australia Podcast. For more information on today's episode, please visit the show notes at www.cpaaustralia.com.au/podcast. Never miss an episode by subscribing to our podcast on Apple Podcasts, Spotify, or Stitcher.