How to Get a List of All Worksheet Names Automatically in Excel

[From (30) How to Get a List of All Worksheet Names Automatically in Excel. - YouTube]

Do you have a large Excel workbook and need to generate a list of all worksheet names?  Instead of manually copying the names of your Excel worksheets, here is a super simple way to do it.

  1. Generate a named range (Formulas -> Name Manager -> New Name
    In the Refers To, add the following formula (you may have to use semi-colons instead of commas):
    =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

  2. In a new worksheet, insert a sequence of numbers from 1 to x in the first column (x being the number of worksheets you have) 

  3. In the second column (cell B1), add the following formula:
    =INDEX(ListNames,A1)
    *Replace ListNames with your named range, and A1 with the first cell in your sequence

Comments