Results 1 to 10 of 10

Thread: Hyperlinked Table of Contents using only formulas

  1. #1
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,087
    Articles
    79
    Blog Entries
    14

    Hyperlinked Table of Contents using only formulas



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #2
    Acolyte Jon von der Heyden's Avatar
    Join Date
    Mar 2011
    Location
    Stellenbosch,South Africa
    Posts
    24
    Articles
    0
    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.
    Attached Files Attached Files
    Last edited by Jon von der Heyden; 2011-03-29 at 08:58 AM.

  3. #3
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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)
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,087
    Articles
    79
    Blog Entries
    14
    Quote Originally Posted by Jon von der Heyden View Post
    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, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,087
    Articles
    79
    Blog Entries
    14
    Quote Originally Posted by Roger Govier View Post
    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!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    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 :-)

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,087
    Articles
    79
    Blog Entries
    14
    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!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    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 :-)
    Attached Files Attached Files

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,087
    Articles
    79
    Blog Entries
    14
    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!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    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 by maninweb; 2011-03-31 at 04:40 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •