Hyperlinked Table of Contents using only formulas

[Ken Puls]

Several years back, Zack Barresse put together a VBA routine that created a table of contents for a file. I thought this was a pretty cool thing, but lately I've been trying to avoid using VBA where I can.

In my massive financial model that I built over the past few months, I'm proud to say that the only VBA I've used is to reinstitute protection on the worksheet at opening, and to enable outlining. I was able to work out a relatively dynamic table of contents system that I thought I'd share here. I'm actually kind of curious if anyone can make any improvements to it.


Basically the gist of it is this:

• I use a defined name to name cell A1 on each worksheet in the file. It is named in the format _x.x_Home, where the x.x is a number format
• I then use my table of contents sheet and build my table of content in the x.x format
• Finally I use a hyperlink formula to build a hyperlink to the individual worksheets

The advantage of this is that:

• No VBA is required
• It's really easy to update when inserting a sheet. (Add a named range, insert a row in the TOC and put in the new index number.)
• If the users changes the name on the worksheets, they still link back to the TOC

I've attached a sample file, and am curious as to any comments.