How to Create a Drop-Down List of Worksheet Names in Excel
Question details
The user wants to create a Data Validation drop-down list that dynamically displays all worksheet names within the current workbook.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a dynamic selection tool to populate nearby cells with specific data (like hardware and model info) based on the selected worksheet.
- Observed behavior
- The user needs a method to extract and list all sheet names into a drop-down menu, which requires specific formulas and Name Manager configurations.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) since the GET.WORKBOOK function is a legacy macro function that will not work in standard .xlsx files.
Use Name Manager and GET.WORKBOOK Function
This method uses a legacy macro function combined with modern array functions to dynamically list all sheet names in a Data Validation drop-down.
Because GET.WORKBOOK is an Excel 4.0 macro function, it cannot be typed directly into a standard cell. It must be defined within the Name Manager first, and then referenced by your Data Validation rule.
Navigate to the Formulas tab on the ribbon and click on 'Name Manager', then click the 'New' button to create a new defined name.
In the 'Name' box, type a descriptive name like 'SheetNames'. In the 'Refers to' box, enter the formula: =TOCOL(TEXTAFTER(GET.WORKBOOK(1)&T(NOW()),"]*")) (Note: adjust the delimiter "]" based on your exact workbook string format). Click OK to save.
Select the cell where you want the drop-down list to appear. Go to the Data tab and click 'Data Validation'.
In the Data Validation dialog, select 'List' from the Allow drop-down menu. In the Source box, type an equals sign followed by your defined name (e.g., =SheetNames). Click OK.
Create Dynamic Drop-Down Lists Easily in WPS Spreadsheet
WPS Spreadsheet offers robust support for Data Validation, Name Manager, and advanced formulas, allowing you to create dynamic drop-down lists and manage complex workbook data effortlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open your macro-enabled workbook.
- 2. Define the Name: Navigate to the Formulas tab, select Name Manager, and define your sheet name formula just as you would in Excel.
- 3. Set up Data Validation: Go to the Data tab, click Data Validation, choose 'List', and set the source to your newly defined name.
Frequently Asked Questions
Why does my GET.WORKBOOK formula return an error?
GET.WORKBOOK is a legacy Excel 4.0 macro function. It cannot be typed directly into a cell. You must define it within the Name Manager and then reference that defined name in your cell or Data Validation rule.
Do I need to save my file in a specific format?
Yes, because GET.WORKBOOK is a macro function, you must save your workbook as an Excel Macro-Enabled Workbook (.xlsm). If saved as a standard .xlsx, the macro function will be disabled and the drop-down will stop working.
How can I use the selected sheet name to pull data?
Once a sheet name is selected from the drop-down, you can use the INDIRECT function (e.g., =INDIRECT("'"&A1&"'!B2")) to dynamically reference specific cells within that chosen worksheet.
