Professor Excel

Comments 10

  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.

    • Henrik Schiffner

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

  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,

  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.


    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,

  7. kyle andrei mazon

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

Leave a comment

%d bloggers like this: