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
and a second one,
Sheets
that refers to
that creates an array of each sheet in the workbook.
And create another name
Now, onto the contents:
In A2, put
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
Now, why doesn’t just
work ? ask MS that… :-P, anyway, back to the subject… put this in B2 and drag it down:
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.
what do you mean “dragging” it down?
followed instructions – but I think I am missing something
using MSExcel 2007
J: Grab the fill handle and pull it down to extend the range and fill down. See
http://www.dailydoseofexcel.com/archives/2004/03/30/secrets-of-the-fill-handle/
http://www.dailydoseofexcel.com/archives/2005/03/09/more-fill-handle-secrets/
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
It worked for me in 2007 J. Here’s something to check.
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.
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.
Thanks, Dick. The changing of quotes did the trick for me. Should have known, use another application that is finicky.
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?
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:
and variations on
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
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”)
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.
Is there any way to show the number of pages with in this “non-vba” way?