How to
Create an Index Worksheet
(Easy access to any worksheet in your workbook)
Sometimes your workbook has dozens of sheets and you want
to easily navigate to a certain sheet without scanning through the entire list.
In that case, you need to build an INDEX sheet that will facilitate this process and save you a great amount of time.
1) Create a new worksheet whose name is INDEX and make it the first worksheet of the workbook.
2) Define a named range in the Name Manager. I called mine “TabNames” but you can choose any valid name.
In the “Refers to” text
box, add this formula:
=LET(a, GET.WORKBOOK(1),MID(a, FIND("]",a)+1,255))
Pic 2: the TabNames formula in the Name Manager
3) In cell A1 type:
=TRANSPOSE(TabNames)
This will create the list of the file’s sheets
in column A, starting from cell A2
Pic 3: creating the list of sheets in column A
This formula adds the hyperlink to the worksheet whose name appears in the
adjacent cell (A2).
Pic 4: Adding the hyperlink for the first sheet
Pic 5: Adding the hyperlinks to all the file’s worksheets
Now you’re good to go. Whenever you want to “jump” to a specific worksheet, click the cell in column B in order to go directly to that worksheet.
6) Don’t forget to update the list whenever you add/modify/delete a worksheet. After each modification you need to refresh the list (select cell A1, press F2 and then: Enter)
And one more trick:
In order to return quickly to the INDEX sheet,
define a shortcut name in the Name Manager’s Defined Names, for example:
O, which refers to Cell A1 in your INDEX sheet, as can
be seen in this screenshot:
Pic 6: Adding the shortcut key to return to the INDEX sheet
Pic 7: going back to the INDEX sheet
This will take you immediately back to the INDEX worksheet.
***
BTW, since the file contains a Macro 4.0 command, you’ll
have to save it as a macro-enabled workbook (.xlsm) instead of a regular
workbook (.xlsx)
