How to Update a Data Sheet from an Excel Drop-Down List in WPS Spreadsheets
Question details
The user wants a status drop-down on a Search worksheet to update the matching record on a Data worksheet, using the serial number as the record key.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains a Search worksheet that retrieves make, model, and status from a Data worksheet by serial number; the selected status should write back to the matching Data worksheet row instead of only displaying on the Search sheet.
- Observed behavior
- A standard data validation list updates only the active Search cell and cannot write the selected value directly to another worksheet.
Enable the Developer tab in WPS Spreadsheets and save the workbook as a macro-enabled file (.xlsm) before adding VBA, otherwise the macro will not be retained.
Use a Worksheet_Change VBA Macro to Update the Matching Data Row
Best when the status drop-down should update the Data worksheet immediately after a selection is made on the Search worksheet.
A standard data-validation drop-down only changes the active cell. To write the selected value to another worksheet, use the serial number as the unique key and run a small VBA macro from the Search worksheet.
Open WPS Spreadsheets, click the File menu > Options > Customize Ribbon, and check Developer. Click OK to display the Developer tab if it is not already visible.
Click File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), and save the workbook so the VBA code can be stored.
Click the Developer tab, then click Visual Basic. In the Project window, double-click the Search worksheet object under Microsoft Excel Objects.
Paste the following macro structure and adjust the sheet name and columns to match your workbook: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$D$2" Then Exit Sub Dim wsData As Worksheet Set wsData = ThisWorkbook.Worksheets("Data") Dim serial As String serial = Me.Range("B2").Value Dim f As Range Set f = wsData.Range("A:A").Find(serial) If Not f Is Nothing Then wsData.Cells(f.Row, 4).Value = Target.Value End Sub
Return to the Search sheet, change the status drop-down to Faulty, and check that the matching serial number row in the Data worksheet now shows Faulty.
Use an Update Button Macro to Write Values Manually
Use this when you want to review the selected status and click an Update button before the Data worksheet is changed.
Update the Data Worksheet with a WPS Spreadsheets Drop-Down and VBA
Use WPS Spreadsheets' built-in VBA editor, data validation, and serial number lookup to make a drop-down selection on the Search sheet update the matching row in the Data worksheet.
- 1. Prepare the workbook: Open the workbook in WPS Spreadsheets and save it as an Excel Macro-Enabled Workbook (*.xlsm) via File > Save As.
- 2. Set up the drop-down: Select the status cell on the Search sheet, click Data > Data Validation, choose List, and enter the status values such as Faulty, In Repair, and Ready.
- 3. Add the matching macro: Click Developer > Visual Basic, open the Search sheet module, and paste a Worksheet_Change or button macro that finds the serial number in the Data worksheet and updates the status column.
- 4. Run and verify: Select a serial number and a status from the drop-down, then confirm the matching Data worksheet row is updated.
Frequently Asked Questions
Can I update another worksheet with a data validation list without VBA?
No. A standard data validation list only changes the cell where it is placed. To write the selected value back to the Data worksheet, use a VBA macro or another script that matches the serial number.
What should I do if the Data worksheet contains duplicate serial numbers?
The macro will update only the first match unless you change it to loop through all matches. To avoid updating the wrong record, keep serial numbers unique and use them as the lookup key.
Why does my drop-down selection not change the Data worksheet?
Formulas and data validation do not create a two-way link between sheets. Add a Worksheet_Change or button macro that finds the serial number in the Data worksheet and writes the selected value to the target column.
Can I open this macro-enabled workbook in Microsoft Excel?
Yes. Save the file as .xlsm and enable macros when prompted. The VBA code is compatible with WPS Spreadsheets and Microsoft Excel.
How do I enable macros after reopening the workbook?
When you open the workbook, click Enable Content or enable macros through the security warning, and make sure the file is saved as Excel Macro-Enabled Workbook (*.xlsm).
