Hyperlinked Table of Contents using only formulas

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
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:
  • 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.
 

Attachments

  • TOC.xlsx
    31.7 KB · Views: 59

Jon von der Heyden

New member
Joined
Mar 25, 2011
Messages
24
Reaction score
0
Points
0
Location
Stellenbosch,South Africa
Website
www.exceldesignsolutions.com
Hi Ken

If using xlsm format is acceptable (i.e. you already have code), then another way that I can think of is to use XLM GET.WORKBOOK function.

Personally I prefer your method, but I thought I would throw this in.

This is quite a quick mash-up so no doubt lots of room for improvement, and I think perhaps I should have used the name to do more of the grinding?

I haven't mashed up a sample as tidy as yours. Perhaps I'll revisit this later today. :)
 

Attachments

  • TOC with Formula.xlsm
    10.2 KB · Views: 29
Last edited:

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
Hi Ken

That's very nice.

I think I would be inclined to make a named range called Sheet, whilst having my cursor in cell B5 of TOC

Code:
=MID(CELL("filename",INDIRECT("_"&TEXT(TOC!A5,"0.0")&"_Home")),FIND("]",CELL("filename",INDIRECT("_"&TEXT(TOC!A5,"0.0")&"_Home")))+1,32)

and then the formula in TOC B5 and copied down would be easier to read and understand

Code:
=HYPERLINK("#_"&TEXT(A5,"0.0")&"_Home",Sheet)
 

Attachments

  • 252-1.png
    252-1.png
    68.2 KB · Views: 15,274

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
If using xlsm format is acceptable (i.e. you already have code), then another way that I can think of is to use XLM GET.WORKBOOK function.

Thanks Jon. The only thing that would concern me there is the logevity of XLM functionality. We haven't been given a definitive date on when it will dissappear, but being that the technology was "superseeded" by VBA in 1995, I think I'd prefer to stay away from it.

Cool solution though, and avoids the named ranges in full. That would definitely be easier to set up. :)
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi Ken,

I like the file and the way you've done it :)

I would like to suggest to store the text format "0.0" in an customizable extra cell or
to replace "0.0" by "0"".""0". Originally the file produces an error on a German version
of Excel and with German Windows regional settings.

Regards :)
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi Mourad,

Can you upload a file showing what you mean? I tried to make that modification, but it doesn't work on the US version of Excel on Canadian regional settings... I'm curious if I'm just not implementing it correctly or...?

Thanks!
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi Ken...

I attached a file with some modifications. Unfortunately, I did not consider yesterday
that replacing "0.0" by ""0"".""0" does not work in all cases, sorry.

So, I added a formula to the toc sheet for creating the correct formats as I did not
found a possibility to cover all cases with a text format.

I tested this on Windows 7 German with Excel 2010 German and with different regional
settings (de, en, fr, es) and also tried Win7/Excel US and Win7/Excel French with
different regional settings.

Regards :)
 

Attachments

  • TOC.2.xlsx
    32.4 KB · Views: 26

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Wow... a lot of work to make it internationally compliant, isn't it? I've been fortunate enough to not have to worry about that in the past, as we don't have any international divisions where I work. I've heard of issues with porting to non-US versions, but haven't paid a ton of attention to them.

Neat stuff!
 

maninweb

New member
Joined
Mar 22, 2011
Messages
88
Reaction score
0
Points
0
Location
Germany
Website
excel-translator.de
Excel Version(s)
2010, 2016, 2016 Insider
Hi Ken,

Thanks :) Yes, I agree, making Excel Sheets work on different languages (Windows and Excel) can be a lot of work. Most of my customers need a version of my apps working on different language systems. So, I am trying to consider international versions at the beginning of the development.

I can only speak for myself; sometimes this can be done in an easy way by just creating a sheet for holding all language specific data (e.g. format strings) and referenciate to this data. However, sometimes it can be better to create a file for each language, especially when the formulas are more complex and searching for the correct language data has a not negligible impact on the performance. Personally, I prefer the first method when ever possible, as the second method needs to change all files when updates or improvements are made.

Regards :)
 
Last edited:
Top