List the Sheet Names with Formula in Excel

List the Sheet Names with Formula in Excel

You can list all of the Excel sheet names within an Excel workbook with a formula using the following steps;

Go to Formulas Tab

Click Name Manager or Press Ctrl F3, to open Name Manager

Click on New

Name: Sheets

Refers to: =GET.WORKBOOK(1)&T(NOW()) 

 

Then Click OK

Click Close

Click cell A1 in worksheet and put this following formula:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROWS(A$1:A1)))) 

Drag the formula down.

The Excel formula should now produce sheet names.