Non VBA Table of contents

Every once in a while there’s a question in the newsgroups on how to create a Table of contents for a workbook. The easiest solution to this is some VBA that loops through the Worksheets() or Sheets() collection, and places that information in the TOC sheet.

There’s another way, using the GET.WORKBOOK() XLM function, and some defined names.

First, insert a sheet (preferably the first sheet in the workbook) and call it Contents.

Now, press Ctrl F3, and define two names, one

BookName

that refers to

=GET.WORKBOOK(16)

and a second one,

Sheets

that refers to

=SUBSTITUTE(GET.WORKBOOK(1),”[“&BookName&”]”,””)

that creates an array of each sheet in the workbook.

And create another name

Now, onto the contents:

In A2, put

=IF(COUNTA(Sheets)>=ROW($A2),INDEX(Sheets,ROW($A2)),””)

and drag it down all the way to say, A50.

Now, the hyperlink in column B gets a little tricky, because the HYPERLINK() function requires us to put the Workbook name in it… kind of strange, but it does, like this

=HYPERLINK(“[BookName.xls]SheetName!Reference”, “FriendlyName”)

Now, why doesn’t just

=HYPERLINK(“SheetName!Reference”, “FriendlyName”)

work ? ask MS that… :-P, anyway, back to the subject… put this in B2 and drag it down:

=IF(LEN(A2),HYPERLINK(“[“&BookName&”]'”&A2&”‘!A1″,”Go to”),””)

After some quick formatting, I ended up with this:

Table of contents

The main advantage of this method: It updates itself automatically when sheets are renamed, moved, deleted, etc.

The main disadvantage; if your workbook grows larger than the number of cells that you originally used, the Table of contents will display incomplete.

Posted in Uncategorized

25 thoughts on “Non VBA Table of contents

  1. Thanks for the tip. John Walkenbach has a tool in his PUP utilities that generates a table of sheet names with the options of each sheet name being a hyperlink, or each sheet name attached to a button/macro to zip right to it. Very nice little utility, and if you buy the source code, you can learn a lot about VBA from his tools and posts like Juan’s as well.

  2. Just a heads up,
    And when suggesting using an xl4 macro command in a defined name and then using that defined name in a cell, it is usually appropriate to advise that copying that cell(s) and pasting to another sheet will result in excel crashing with a GPF in versions of excel before xl2003.

  3. Thanks for this. I have a couple of additional points:

    1. My table of contents does not refresh when sheets are added or deleted – what do I need to do (other than redefining the name ‘Sheets’?

    2. If you want to pick up a particular value from a sheet you can use a combination of the ADDRESS function and the INDIRECT funtion eg

    =IF(LEN(A9),INDIRECT(ADDRESS(17,5,1,1,$A9)),””)

    retrieves cell E17 from the sheet shown in cell A9.

  4. Nicola,

    When I wrote this, I remember it did update. But you can just force it using Control Alt F9, which will recalculate the workbook.

  5. This won’t work in Excel 2003 since the XLM functions are no longer supported. Can someone of Excel gurus out there pls. suggest a practical (non-VBA, Excel 2003) workaround leading to the same result?

  6. zzonyx, I just tested it with Excel 2007 and it works as expected. And as far as I remember, Excel 2003 does support XLM functions.

  7. I’ve gotten this to work, which is great… but… I also have pivot table charts that I’d like to connect to, but for whatever reason, it does not work on them… Help!

  8. And heres a version to make it only using one cell and not needing the a2 cell.
    Put it anywhere on the content sheet and drag it down. then copy and organize them to where you want them.
    =IF(LEN(IF(COUNTA(Sheets)>=ROW($A2),INDEX(Sheets,ROW($A2)),””)),HYPERLINK(“#”&IF(COUNTA(Sheets)>=ROW($A2),INDEX(Sheets,ROW($A2)),””)&”!A1?,IF(COUNTA(Sheets)>=ROW($A2),INDEX(Sheets,ROW($A2)),””)),””)

    Important Notes: (this will save you a lot of time)

    the sheet names must not contain spaces or strange symbols like & % etc.

    And thanks for the help, its the only site on the net to have this.

  9. what do you mean “dragging” it down?

    followed instructions – but I think I am missing something

    using MSExcel 2007

  10. Ok – learned about dragging.
    Followed the instructions above and double checked, but A2….A50 is blank, B2…B50 is “FALSE”

    Does this work on MSExcel 2007?

    Thanks for the speedy reply!!

    John

  11. It worked for me in 2007 J. Here’s something to check.

    =SUBSTITUTE(GET.WORKBOOK(1),”[“&BookName&”]”,””)

    In the above name, the double quotes got screwed up – the second to last and third to last double quotes. If you copied and pasted that formula, you may need to replace those goofy characters with real double quotes.

  12. Even when I copied that formula and fixed the double quotes, WordPress changed them back to goofy. I’m not sure why those two and not the others. Just check all your double quotes.

  13. Thanks, Dick. The changing of quotes did the trick for me. Should have known, use another application that is finicky.

  14. Andres. Tried your solution also.
    MSExcel corrected it to:
    =IF(LEN(IF(COUNTA(Sheets)>=ROW($A2),INDEX(Sheets,ROW($A2)),””)),HYPERLINK(“#”&IF(COUNTA(Sheets)>=ROW($A2),INDEX(Sheets,ROW($A2)),””)&”!A1?,IF(COUNTA(Sheets)>=ROW($A2),INDEX(Sheets,ROW($A2)),””)),”””))

    which yields goofy results and no hyperlink action

    #Table of Contents!A1?,IF(COUNTA(Sheets)>=ROW($A2),INDEX(Sheets,ROW($A2)),”)),”

    Am I missing something?

  15. If I wanted to reference a specific cell on the sheet that is listed in the TOC, how would I do that? I’ve tried several different things:

    =IF(LEN(A9),INDIRECT(ADDRESS(17,5,1,1,$A9)),‚Äù”)

    and variations on

    =IF(LEN(A3),TEXT(INDIRECT(ADDRESS(1,5,1,$A3)),”$#,##0.00″),””)

    but I keep getting a #VALUE! error. I don’t get it. Basically I just want to have a formula that will get the contents of a cell on the sheet that is named in cell $A3, using your wondrous TOC function above.

    Thanks in advance for any help you’re able to give!
    -Athena

  16. Hi,

    For internal hyperlinks in worksheet I use function since does not brake whenever the name of worksheet is changed:

    E.g. (Just Change ‘Sheet2!A1’ and ‘Link text’)
    =HYPERLINK(“#”&CELL(“address”,Sheet2!A1),”Link text”)

  17. How would I have all sheets hidden when not the active sheet so the only sheet visible is the table of contents?

    So click a table of contents link and it brings up that sheet then when you go back to the table of contents that sheet goes hidden again.

  18. Pingback: List Sheet Names

Leave a Reply

Your email address will not be published. Required fields are marked *