![]() ![]() ![]() 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |