- Joined
- Mar 13, 2011
- Messages
- 2,531
- Reaction score
- 6
- Points
- 38
- Location
- Nanaimo, BC, Canada
- Website
- www.excelguru.ca
- Excel Version(s)
- Excel Office 365 Insider
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:
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
- 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