Professor Excel

Comments 15

  1. Brian Canes

    Here is a way with an Excel4 defined name and a couple cell formulae.
    Define TabNames=GET.WORKBOOK(1)&REPT(NOW(),)
    In a cell enter the following formula
    =”B O O K = “&TabNames&” “&” S H E E T S = “&SHEETS()
    In the cell below that enter the following formula
    =IF(ROW(A2)>SHEETS(),REPT(NOW(),),SUBSTITUTE(HYPERLINK(“#'”&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(INDEX(TabNames,ROW(A2)),” “,CHAR(255)),”]”,REPT(” “,32)),32))&”‘!A1″),CHAR(255),” “))
    and fill down until you get empties.
    Regards
    Brian

    • Henrik Schiffner

      Dear Brian,
      Thanks a lot, that’s a good way! I’ll add it.
      Best regards,
      Henrik

  2. Hayden

    Is there a way with Method 2 of then being able to hyperlink back from the sheets to the table of contents. That would be amazing.

    • Henrik Schiffner

      Dear Hayden,

      There are two simple approaches.

      If you are sure that – let’s say – cell A1 is unused on each sheet, prepare a link to the table of contents in cell A1 on one of the sheets.
      In order to achieve this, select the cell and press Ctrl + K on the keyboard. In the “Insert Hyperlink” window select “Place in This Document” and define the link to the table of contents. Confirm with OK.
      Copy the cell and select all worksheets which should link to the table of contents. Paste the copied cell with Ctrl + v. Now, each worksheet should have a link to the table of contents.

      Alternatively prepare a text box, also linking to the table of contents sheets (similar to the first method above). Copy it and paste it on each worksheet separately.

      Does that help?

      Best regards,
      Henrik

  3. nhetnai

    For a faster way to create a table of contents, see Create a table of contents automatically.

  4. Ralse

    The VBA only seems to work just once and gives an error on ‘already existing name for sheet’ when used again.
    Method 2 is more durable for end users unless method 3 refreshes itself when a new sheet is added.

  5. i4laughlin.com

    Repeat steps 3 and 4 for all the levels that you want to display in your table of contents.

  6. Craig Seifferth

    I tried Method 2: Use formulas for a table of contents multiple times and when pasting the second, longer formula that begins with

    =IF(ROW(A1)>SHEETS(),REPT(NOW . . . .

    all I get is this error in the cell: #NAME?

    Any suggestions?

    • Henrik Schiffner

      Hi Craig,
      For me the method worked so I can’t really reproduce the error. But the #NAME error usually indicates – as the word says – a problem with a name. And because this method uses a named range, maybe there is a problem? Also have you checked if you’ve spelled all formula names correctly?
      Best regards,
      Henrik

  7. kyle andrei mazon

    i know this already…… but i learn some ideas in order to work fast…….!!!

  8. Tomasz

    Dear Henrik,

    thank you very much for Method 3.
    I have a question :
    i was trying to make such TOC for sheets which are visible.
    In excel file I have about 30 sheets, but depends from case I choose, for printing I have only 7-10 visible.

    If I use method 3 then all sheets (about 30) are taken for TOC, but I would like to have only visible.
    I’m not expert in VBA, I was trying to play with parameter .Visible but it doesn’t work.
    If you can give some tip I will be very grateful.

    Best Regards,
    Tomasz.

    • Henrik Schiffner

      Hi Tomasz,
      Without trying it: You could add something like

      If PROFEXWorksheet.Visible = True Then

      after

      For Each PROFEXWorksheet In Worksheets

      and

      End If

      before

      Next

      Does that work?

      Best regards,
      Henrik

    • Henrik Schiffner

      …and of course my add-in provides this functionality… 😉

  9. Kgashane

    Hi Henrik

    Thanks a lot for this. How do I get it to list the contents of Cell A1 of each tab, instead of the tab name?

Leave a comment

%d bloggers like this: