Create a Dependent Dropdown List in WPS Spreadsheets
Question details
The user needs to create a stock description dropdown in WPS Spreadsheets and a size dropdown that updates based on the selected description, without maintaining one large static list.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a stock and size entry sheet and wants the Size dropdown to depend on the selected Stock Description.
- Observed behavior
- The current setup would require one large static dropdown list or manual size entry; the user wants an editable or dependent dropdown that updates automatically.
Prepare your stock descriptions and matching size lists in a separate area, and create named ranges such as Stock1_Size, Stock2_Size, and Stock3_Size. The named range must match the description plus "_Size" for the formula to work.
Use Dependent Data Validation Lists for a No-Code Dropdown
Use this method when you want a formula-based solution without macros and your size options are organized by stock description.
Create named ranges for each stock description's size list, then set the Size cell's data validation to a formula that looks up the range matching the selected Stock Description.
Select the cells containing the sizes for one stock description, go to the Formulas tab, click Name Manager, and define a name such as Stock1_Size. Repeat for each stock description.
Select the Stock Description cell, go to Data > Validation, set Allow to List, and enter Stock1,Stock2,Stock3 as the source.
Select the Size cell, go to Data > Validation, set Allow to List, and enter =INDIRECT(A2&"_Size") as the source, where A2 is the Stock Description cell and the named ranges match the description plus "_Size".
Use VBA Worksheet_Change for a Fully Dynamic Dropdown List
Use this method when you need the size validation list to rebuild automatically whenever the Stock Description changes and you are comfortable enabling macros.
Create Dependent Dropdown Lists in WPS Spreadsheets Without a Large Static List
WPS Spreadsheets lets you build dependent dropdown lists with data validation and named ranges, so Size options update automatically from the selected Stock Description. You can also use VBA for more dynamic behavior, all within a free and familiar spreadsheet interface.
- 1. Open your workbook in WPS Spreadsheets: Download and install WPS Office, then open the Excel workbook or create a new spreadsheet in WPS Spreadsheets.
- 2. Name the size ranges: From the Formulas tab, open Name Manager and create named ranges for each stock description's size list.
- 3. Set up the dependent Size dropdown: Select the Size cell, go to Data > Validation, and use =INDIRECT(A2&"_Size") referencing the Stock Description cell.
- 4. Test the dropdown: Change the Stock Description and verify that the Size list updates automatically.
Frequently Asked Questions
Can I create a dependent dropdown in WPS Spreadsheets without VBA?
Yes. Use Data Validation with named ranges and an INDIRECT formula. Create a named range for each stock description's sizes, then set the Size cell validation source to =INDIRECT(A2&"_Size") if A2 is the Stock Description cell.
Why does my dependent dropdown show an error or blank list?
The most common cause is a mismatch between the selected stock description and the named range. Check that the range name exactly matches the description plus "_Size" and contains no invalid spaces or characters.
How do I make the dropdown editable instead of choosing from a fixed list?
Data Validation dropdowns are selection-based. If users need to type new sizes, uncheck the Show error alert option in Data Validation, or use VBA to update the source list dynamically.
Does WPS Spreadsheets support macros for dynamic dropdowns?
Yes, WPS Office supports VBA macros. Open the VBA editor from the sheet tab, add a Worksheet_Change event, and save the workbook as a macro-enabled file.
Can these dropdowns work in Microsoft Excel after editing in WPS?
Yes. WPS Spreadsheets preserves data validation and named ranges in .xlsx format, and macro-enabled files can be saved as .xlsm for Excel compatibility.
