To do so, just click Formulas > Name Manager.

Next, we want to create a Defined Name that we can use in the next step. You can view or edit the table name as desired by using the Table Design > Table Name field. If this is the first table in the workbook, it probably has a name of Table1. The resulting table should look something like this: Then, convert that ordinary range into a table by selecting it and selecting the Insert > Table command. 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.īegin by typing the sheet names into some cells, like this: If there are only a few sheets, creating the table manually is the easiest. Let’s start by creating a table with the sheet names. Step 1: Create a Table with the Sheet Names

Bonus Step: Optionally, use Power Query to create the list of sheet names dynamically.

Step 4: Use the INDIRECT function to create a reference to the selected sheet.

Step 3: Create a drop down with data validation.

Step 2: Create a defined name that references the table name.

Step 1: Create a table with the sheet names.

On a summary sheet, we want to create a drop down so a user can pick one of the department sheets, like this:Īnd 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: We have a workbook with multiple sheets … perhaps one sheet for each department like this:Įach sheet has the same basic data structure, like this: Thanks Sara for your question! Videoįirst, let’s be clear about what we are trying to accomplish. Plus, as a bonus, I’ll demonstrate how we can dynamically generate the list of sheet names using Power Query. In this post, I’ll demonstrate one such solution that uses a defined name, data validation, and the INDIRECT function. As this is Excel, there are a variety of alternatives to accomplish this. 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.

Excel drop down list