List of Worksheets in a Drop Down
In this post, I’ll answer a question I received from Sara about how to create a drop down with each worksheet name and reference it in a formula to retrieve values from the selected sheet. As this is Excel, there are a variety of alternatives to accomplish this. In this post, I’ll demonstrate one such solution that uses a defined name, data validation, and the INDIRECT function. Plus, as a bonus, I’ll demonstrate how we can dynamically generate the list of sheet names using Power Query. Thanks Sara for your question!
First, let’s be clear about what we are trying to accomplish. We have a workbook with multiple sheets … perhaps one sheet for each department like this:
Each sheet has the same basic data structure, like this:
On a summary sheet, we want to create a drop down so a user can pick one of the department sheets, like this:
And when a sheet is selected, we want to use the selected sheet name in a formula to reference values on the selected sheet, like this:
So, let’s get to it.
Overall, here is the game plan:
- Step 1: Create a table with the sheet names
- Step 2: Create a defined name that references the table name
- Step 3: Create a drop down with data validation
- Step 4: Use the INDIRECT function to create a reference to the selected sheet
- Bonus Step: Optionally, use Power Query to create the list of sheet names dynamically
Step 1: Create a Table with the Sheet Names
Let’s start by creating a table with the sheet names. If there are only a few sheets, creating the table manually is the easiest. If you have a ton of sheets, be sure to check out the final Bonus step which demonstrates how to use Power Query to create the list of sheet names dynamically.
Begin by typing the sheet names into some cells, like this:
Then, convert that ordinary range into a table by selecting it and selecting the Insert > Table command. The resulting table should look something like this:
If this is the first table in the workbook, it probably has a name of Table1. You can view or edit the table name as desired by using the Table Design > Table Name field.
Step 2: Create a Defined Name
Next, we want to create a Defined Name that we can use in the next step. To do so, just click Formulas > Name Manager.
In the resulting Name Manager dialog, click New and then create a new name such as SheetList and set it equal to the table name in the previous step (eg, =Table1), like this:
Note: the equal sign = in front of the table name is required.
Step 3: Create a Drop Down
Now we can create the drop down. Select the cell for your drop down, and select Data > Data Validation. In the resulting Data Validation dialog, you want to Allow a List, and the Source is =SheetList (or whatever name you defined in the previous step), like this:
Note: the equal sign = in front of the SheetList name is required.
When you click OK, you should have a functioning drop down, like this:
Next, we want to reference the selected sheet name in our formula. To do that, we’ll want to use the INDIRECT function.
Step 4: Use INDIRECT
The INDIRECT function converts a text value into an actual Excel reference.
Let’s get warmed up with a simple example of a cell reference. We could pass a text string “A1” into the INDIRECT function and it would return an actual reference to cell A1. This formula would look like this:
Let’s take the next step and figure out how to reference a cell on another sheet. If we type an equal sign = into any cell, and then click a cell on a different worksheet, we can inspect the resulting reference to see how Excel does this.
For example, the reference to cell A1 on a sheet named Dept A looks like this:
Two things to note:
- First, we use an exclamation point ! between the sheet name and the cell reference
- Second, we enclose the sheet name in single quotes
So, let’s put this all together.
The desired sheet name is selected from a drop down. Let’s say that drop down is in cell C5, like this:
And let’s say we want to retrieve the Manager from the selected sheet, which is stored in B4, like this:
We would then write a formula using INDIRECT, like this:
=INDIRECT(" ' " & C5 & " '!B4 ")
Note: I added a bunch of extra spaces and some underlines to try to make it easier to read. These spaces should be removed from your actual formula.
Let’s understand what is going on. First, we use INDIRECT to convert a text value into an actual Excel reference.
The text value includes three components, all joined together with the concatenation operator &:
- The opening single quote (enclosed in quotes) for the sheet name
- The reference to the drop down cell (which has the sheet name)
- The closing single quote for the sheet name, then the exclamation point to separate the sheet name from the cell reference, and finally the desired cell reference (enclosed in quotes)
When we hit Enter, the formula retrieves the value from B4 from the sheet selected in the drop down … yay!
What if we wanted to sum an entire column from the selected sheet? No worries. We can essentially use the INDIRECT function in formulas where we would use a normal cell or range reference.
So, to sum the entire C column on the selected sheet, we write a formula like this:
=SUM( INDIRECT("'" & C5 & "'!C:C") )
Note: I added extra spaces to try to make the formula easier to read.
Bonus Step: Use Power Query to create a Dynamic Sheet List
When there are only a handful of sheet names, typing them in manually is the fastest and simplest. But, what if you have a ton of sheet names, or you are adding or removing sheets frequently from the workbook. In this case, we can have Power Query generate the list of sheet names for us.
One way to do this is to click Get Data > From File > From Workbook from the Get & Transform command group. Next, we browse to the workbook. In the resulting Navigator dialog, we just pick any sheet (it doesn’t matter which one at this point because we will change it later).
Next we click the Transform Data button to open the Power Query Editor.
We use the X to delete all applied steps except for the Source step. So, at first the Applied Steps list may look something like this:
Then, click the X to delete all but the Source step:
At this point, you will see a list of any Sheets, Tables, and Defined Names that are in the workbook. These are identified in the Kind column:
Since we only want to return a list of Sheets, we filter the Kind column to keep Sheet rows only.
Note: even if you have only Sheet rows, you still want to apply a Text Filter = Sheet so that new tables or names that are created later will be excluded.
At this point, we should see a list of the worksheets in the Name column:
Since we want to exclude the Summary sheet from the drop down, we’ll just use a filter to exclude it.
Also, we only want to return the Name column to Excel, so, we right-click the Name column header and select Remove Other Columns.
Next, we click Close & Load To … and select Table. We can insert the table into either a new or existing sheet as desired.
The resulting table should look like this:
Now, to tell the drop down to reference this new table instead of the original table (blue), we go back to the Name Manager (Formulas > Name Manager). We Edit the SheetList name and change it to reflect the Power Query results table (green). You can determine the name of the table by clicking it and looking at the Table Design > Table Name field.
Once done, we can safely delete the original table (blue) from the workbook.
Going forward, when we change sheet names, add new sheets, or remove sheets, we can (1) Save the workbook and then (2) right-click the Power Query results table (green) and Refresh. This will populate the results table with the current sheets, and the drop down will reflect them. Yay … we did it!
NOTE: you will need to Save the workbook for any changes in the sheet names to be reflected. In other words, when you Refresh the table, Power Query looks at the saved version and excludes any changes that have been made since you last saved.
NOTE: if you didn’t filter for type Sheet as suggested, your list of sheet names may include table names and defined names over time as they are added, including the results table. If this happens, you can edit the query and apply a filter to include only Kind=Sheet.
NOTE: if you rename or move the location of this workbook, you’ll want to edit the query’s Source step to reference the new name or location.
If you have any improvements or other approaches to this, please share by posting a comment below … thanks!
Sample file: SheetDropDown.xlsx
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.