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