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:
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.
You can omit the workbook name from the hyperlink by using the #:
HYPERLINK(”#’” & Sheetname & “‘!A1″)
Ah… very cool Jan Karel ! Thanks for that tip, didn’t know that.
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.
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.
So Excel 2003 is the first version of Excel that’s really compatible with XLM? I thought they said XML at first.
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.
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.
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?
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.
JPG: Tested in 2003 and it works fine.
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!
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.