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.
jkpieterse:
You can omit the workbook name from the hyperlink by using the #:
HYPERLINK(”#’” & Sheetname & “‘!A1″)
13 May 2005, 1:43 amJuan Pablo González:
Ah… very cool Jan Karel ! Thanks for that tip, didn’t know that.
13 May 2005, 8:15 amKevin DeWhitt:
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.
13 May 2005, 8:26 amTom Ogilvy:
Just a heads up,
15 May 2005, 3:00 pmAnd 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.
Jon Peltier:
So Excel 2003 is the first version of Excel that’s really compatible with XLM? I thought they said XML at first.
16 May 2005, 6:05 amNicola Graham:
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.
2 November 2005, 9:02 amJuan Pablo González:
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.
2 November 2005, 1:26 pmzzonyx:
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?
22 May 2007, 1:58 amJuan Pablo Gonzalez:
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.
22 May 2007, 10:42 amDick Kusleika:
JPG: Tested in 2003 and it works fine.
22 May 2007, 12:23 pmKirsten:
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!
24 May 2007, 3:41 pm