Here is a 2 step technique to retrieve the names of all sheets in Excel workbook:
Step:1. First define a named range using the below formula:
Step: 1a. Click “Define Name” command in “Define Names” group under “Formulas” ribbon tab to invoke “New Name” dialogue
Step: 1b. Let the name of the range be “SheetNames”
Step: 1c. Let the name refer to the range defined by the formula: = Get.Workbook(1)
Step: 1d. Click OK
Step:2. Write the below array formula simply referring to the range name defined in Step 1 to get the names of all the sheets in the current workbook listed:
– Since there are 3 sheets in the workbook in the screenshot, select any three consecutive cells in a row.
– Enter “=” to start writing the formula and then type the range name defined in Step.1 as in the below screenshot:
– Hit CTRL + SHIFT + ENTER , to enter the formula as array formula and the list of all sheet names are listed. See the below screenshot:
Note that the sheet names are listed along with the workbook name. You can write a formula using Excel’s built-in functions to extract only sheet name from these names that are qualified by the workbook name.
Happy learning..!! Do leave your comments.