Set an Excel Drop-Down Default from Another Cell in WPS Office Spreadsheets
Question details
The user wants a data-validation drop-down cell to default to a value from another cell in the same row, with the relationship copied down to additional rows.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is setting up a spreadsheet where each row has a source cell and a validated drop-down cell that should show the corresponding source value by default.
- Observed behavior
- The drop-down cell should display the referenced value while keeping its data-validation list, and the relative reference should update automatically when the setup is copied down.
Create the data-validation drop-down list in the target cells first, and confirm the source cell in the same row contains a value that is also in that list. Use a relative reference such as =I5 so each copied row points to its own source cell.
Use a Relative Cell Reference as the Drop-Down Default
Use this method when the default value is stored in a cell in the same row and you want the setup to copy down cleanly.
In WPS Office Spreadsheets, a formula can supply the displayed value for a cell without removing its data-validation list. If the formula result matches an allowed list entry, the cell remains validated and the list arrow still appears when the cell is selected.
Because the reference is relative, copying the cell to another row automatically changes the referenced source cell to the matching row.
Click the cell where the data-validation list is already applied, for example H5.
Type =I5 in the formula bar, replacing I5 with the address of the source cell in the same row, then press Enter.
Select the cell again and check that it displays the value from the referenced cell and still shows the drop-down arrow.
Drag the fill handle or copy H5 and paste into the cells below. WPS updates the relative references automatically, for example =I6, =I7, and so on.
Use WPS Office Spreadsheets to Set Row-Based Drop-Down Defaults
WPS Office Spreadsheets lets you enter a relative formula such as =I5 in a validated cell, so the drop-down default follows the value from another cell in the same row and stays linked when copied down. You can work in .xlsx workbooks without switching to a different spreadsheet environment.
- 1. Open the workbook: Open your file in WPS Office Spreadsheets and select the target drop-down cell, for example H5.
- 2. Create the validation list if needed: If the cell does not already have validation, go to Data > Validation, choose List, and set the allowed entries.
- 3. Enter the relative default formula: Type =I5 in the formula bar, replacing I5 with the source cell in the same row, and press Enter.
- 4. Copy the setup down: Select the cell and use the fill handle to copy the formula and validation down the rows so each row references its own source cell.
Frequently Asked Questions
Will entering =I5 in a validated cell remove the drop-down list?
No. The data-validation list remains on the cell. WPS Office accepts the formula result when it matches an allowed list entry, and the drop-down arrow still appears when the cell is selected.
How do I apply this to many rows at once?
Select the first drop-down cell, then drag the fill handle or copy and paste it down the column. Relative references update automatically, so H5 using =I5 becomes H6 using =I6, H7 using =I7, and so on.
What should I check if the referenced value is rejected?
Make sure the referenced cell contains a value that exactly matches one of the entries in the data-validation list. Hidden spaces, case differences, or values outside the list can trigger a validation error.
Can I use this method in an existing Microsoft Excel workbook opened in WPS Office?
Yes. WPS Office Spreadsheets supports data validation and relative cell references in .xlsx and .xls workbooks, so you can edit the file without changing the validation structure.
Do I have to create the drop-down list before entering the formula?
It is recommended to create the list first so validation rules are already in place. If you enter the formula first and then apply validation, WPS still evaluates the formula result against the list, so you do not need to rebuild it.
