pivotronics

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.