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.

[Jon von der Heyden]

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.

[Roger Govier]

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

=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

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

[Ken Puls]

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.

[Ken Puls]

I think I would be inclined to make a named range called Sheet, ... and then the formula in TOC B5 ... would be easier to read and understand

I like it! Thanks Roger!

[maninweb]

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]

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]

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 :-)

[Ken Puls]

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]

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 :-)