• 08/26/2018
    Excel: List All Worksheets in a Workbook on One Tab

    If you are working with dozens of worksheets that are formatted in identical fashion (think of a company that has multiple locations with the same line items), then the ability to list all of these worksheets on one tab can be extremely helpful. As you will see in the template available for download, this permits retreiving data with the =INDIRECT function from each individual worksheet.

    To create a list of tab names in your workbook follow these two steps:

    Step 1:

    Name the following formula "Sheets"

    =(GET.WORKBOOK(1))&T(NOW())

    Note: If you are not familiar with this process select Formulas > Name Manager > New and then input the formula.

    Step 2:

    Then use this formula to retreive all worksheet names:

    =INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A1))

     

    Download Template

     

    List all Worksheets in a Workbook.

     

    For more information on the =INDIRECT function: Referencing Data on a Specific Worksheet with =INDIRECT.