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

Mastering LOOKUPs in Excel learning Series

Here I thought of compiling a learning series covering the basics to classics of Excel Lookup functionality. So I have divided them into parts organized in some logically organized fashion. However, you are free to access and learn them in any order.

Part 1: Introduction to VLOOKUP

Part 2: VLOOKUP: Approximate match example

Part 3: VLOOKUP: Right to Left Lookup

Part 4: INDEX and MATCH combination – An alternative to VLOOKUP

Part 5: Cascading validation lists

Part 6: Nested VLOOKUP

Part 7: Troubleshooting VLOOKUP errors

Part 8: VLookup Refresher

If you have come across any challenges while using VLOOKUP that are not addressed in any of these parts of this learning series, do share them in the comments.

I will be enhancing this post as and when adding a new parts relating to Lookups in Excel and will enable each part as hyperlink as and when I post the content of that post on my blog.

Want to learn more Excel tips and tricks ? Follow my blog by entering your email and then hitting on FOLLOW button at the top right.

Happy learning and have an adventurous journey to mastering Lookups in Excel.

Introduction to VLOOKUP in MS Excel

Hi friends… one of the important features of Excel which every user of Excel is expected to be aware of, is VLOOKUP function. Here I take you through the nuts and bolts of VLOOKUP:

What does VLOOKUP do ? VLOOKUP function looks up (searches)  a value in one column and then returns the corresponding value in the same row from another desired column.

It’s similar to searching for the phone number of a person from a phone directory where you would first search for the name of the person and then move across that row towards the phone number column and then take a note of the value found in that phone number column.

Signature of the function:                                                                                                

VLOOKUP(lookup_value, table_array, column_index_num, [table_range])

Explanation of arguments of the function:

lookup_value: This argument indicates WHAT is the value that you want to lookup or search.

table_array: This argument indicates WHERE do you want to lookup or search. This argument is also known as lookup table. The lookup value will be searched ONLY in the FIRST column of the lookup table.

column_index_num: This argument indicates value from WHICH column in the lookup table is to be returned.

table_range: This argument indicates whether the search process need to be exact type or approximate type. 0 (zero) or FALSE indicates exact match, 1 or TRUE indicates approximate match. This argument is optional. Although FALSE is most frequently used, TRUE is the default value for this argument, if nothing is specified.

An example is better than a precept.

So, lets understand the working of VLOOKUP function with a couple of examples using some fictitious data:

Example 1.

Scenario: Here are some customer names with their phone numbers alongside maintained by an analyst. The requirement is that the manager will determine the lucky customer each month by some lucky draw and ask for his/her phone number from the analyst, so that the customer may be contacted on Phone.

Now the analyst do not want to search for the customer in that long list of customer and phone numbers manually as we do in a phone directory. He just wanted to enter the name of the person in the cell beside “Best Employee” (i.e., in G2) so that the phone number of the customer is automatically displayed in the cell beside “Phone Number” (i.e., in G3), as in the below screenshot.

Now, lets write the VLOOKUP formula in cell G3 to return the Phone Number of the Lucky Customer  present in cell G2 by looking up or searching for that customer in the lookup table spread across the range – B3:C13.

=VLOOKUP(G2,B6:C16,2,FALSE)

VLookup_1_1

Explanation:

WHAT to search: cell G2 has the name of the customer that you want to search.

WHERE to search: the first column in the range B3:C13 is the place where the customer name is to be searched.

value of WHICH column of the range in the corresponding row to return: proceeding from left to right in the range and numbering the columns starting from number 1, in mind, Phone Number column gets number 2. since we want the value from Phone Number column to be returned finally, we specify its enumerator, i.e., 2 here.

Last argument indicating Match Type: Since, we want search for the exact name of the customer in cell G2 from the first column of the lookup table, we will indicate 0 or FALSE here.

Now when you press enter after entering the formula in cell G3, it initially returns a “#NA” since we haven’t yet entered any Lucky Customer name in cell G2.

Testing:

Now lets test our VLOOKUP formula by entering a name of a customer as Lucky Customer in cell G2 to see if it returns the correct phone number of that customer.

I entered “Rathod” as Luck Customer and it correctly returned the respective Phone Number as highlighted the record of this customer in the lookup table in yellow:

VLookup_1_2

* Remember that VLOOKUP does case insensitive search. Hence, in the above example, if you have entered “rathod” in the place of “Rathod”, you would still see the right value being returned.

Caveats: If the column in the lookup table where the lookup value to be searched is not the first column of the lookup table, VLOOKUP wouldn’t work.

Hope you enjoyed reading this post..!! Want to learn more variations of VLOOKUP addressing more challenging business data analysis problems …..? Follow my blog by just entering your email id and hit on follow button at the top right of this blog post..