Trick for Listing the names of all sheets in Excel workbook

List of Sheet Names output

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

New Name Dialogue
New Name Dialogue

 

 

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:

Array Formula referring the defined Name
Array Formula referring the defined Name

 

– Hit CTRL + SHIFT + ENTER , to enter the formula as array formula and the list of all sheet names are listed. See the below screenshot:

List of Sheet Names output
List of Sheet Names output

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.

Advertisements

Microsoft Office Excel 2003 menu to 2010 ribbon mapping Workbook and Interactive Reference Guide

Friends..

Here is the link where Excel workbook containing the mapping between menu of Excel 2003 and Ribbon of Excel 2010 can be downloaded:

http://office.microsoft.com/en-in/templates/results.aspx?qu=Excel%202010%20Menu%20to%20ribbon%20reference%20workbook&queryid=6b2d60e7%2D5451%2D4a60%2Daee7%2Dc4f4983aa767#ai:TC101842354

Here is the link where an interactive guide showing the the mapping between menu of Excel 2003 and Ribbon of Excel 2010 can be downloaded:

http://www.microsoft.com/en-us/download/details.aspx?id=16642

Hope you will find this helpful.. 🙂

Microsoft Office Excel 2003 menu to 2007 ribbon mapping Workbook and Interactive Reference Guide

Friends..

Here is the link where Excel workbook containing the mapping between menu of Excel 2003 and Ribbon of Excel 2007 can be downloaded:

http://office.microsoft.com/en-in/templates/excel-2007-menu-to-ribbon-reference-workbook-TC010212862.aspx?CTT=5&origin=HA010149151

Here is the link where an interactive guide showing the the mapping between menu of Excel 2003 and Ribbon of Excel 2007 can be downloaded:

http://www.microsoft.com/en-us/download/details.aspx?id=14650

Hope you will find this helpful.. 🙂