יום שלישי, 20 ביוני 2023

Excel - How to create an Index Worksheet

 

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 ManagerI 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 1: defining the TabNames formula in the Name Manager

 

          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

 4)    Type the following formula in cell B2: =HYPERLINK("#"&A2&"!A1")

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

 5)    Double-click the lower right corner of cell B2 to propagate the formula to the rest of the list

                   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

 Now, whenever you want to go back to the INDEX sheet, Press F5 (or: CTRL+G) to display the GO TO pane, type: O In the Reference Text Box and press: OK


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)