Professor Excel

Comments 7

  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.

Leave a comment