VBA & Macro Problems

How to Synchronize an Unfinished Work List with the Main Sheet in Excel

Maira Mehtab Sep 21, 2026 868 views

Question details

The user wants to display only unfinished work orders on a secondary worksheet, where edits made to these rows automatically write back to the primary source sheet.

Product: Excel
Device & OS: not provided
Scenario: Managing work orders by giving users a filtered list of pending tasks to edit without making them search through the entire master database.
Observed behavior: Standard filtered or copied lists in Excel are one-way; they display data but do not automatically sync user edits back to the original source rows.

Before you start

Ensure every row in your main data sheet has a unique identifier, such as a unique Work Order ID, so automation scripts can accurately locate and update the correct record.

Solution 1Recommended

Use a VBA Worksheet_Change Macro for Two-Way Synchronization

Since standard Excel formulas cannot write data back to a source, you must use VBA to capture user edits on the unfinished list and apply them to the main sheet based on a unique ID.

This method requires a macro to monitor the secondary sheet for changes. When a user edits a cell, the code finds the corresponding unique ID in the main sheet and updates the same field.

Format Data as a Table

Ensure your main worksheet data is formatted as an Excel Table (Insert > Table) and contains a column for a unique ID.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) window.

Insert the Event Macro

In the Project Explorer, double-click the secondary worksheet (Unfinished Work List) and paste a 'Worksheet_Change' event script designed to match the target cell's unique ID with the main sheet.

Save as Macro-Enabled

Go to File > Save As and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure your synchronization code continues to run.

VBA Knowledge Required: Writing the exact VBA script requires knowing your specific column layouts and sheet names. The script must utilize the Range.Find method to locate the unique ID.

Solution 2

Migrate Two-Way Data Entry to a Database Software

If VBA automation is not possible or too complex to maintain, migrating the workflow to a relational database like Microsoft Access is the most reliable method for two-way edits.

Advanced Spreadsheet Management

Manage and Sync Worksheets Seamlessly with WPS Spreadsheet

WPS Office provides robust spreadsheet capabilities, including full support for VBA macros, allowing you to easily synchronize data across multiple sheets and manage work orders efficiently.

1. Open Your File in WPS: Launch WPS Spreadsheet and open your existing work-order workbook.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on 'Visual Basic' to open the macro editor.
3. Apply Sync Code: Paste your synchronization macro into the specific worksheet module where users will make their edits.
4. Save the Document: Save your workbook in the Macro-Enabled Workbook format to preserve the automation functionality.

Fully compatible with Microsoft Excel formats, including Macro-Enabled Workbooks (.xlsm).Built-in VBA editor for running and writing custom automation scripts.Lightweight architecture ensures fast performance even with large data tables.Free to use for everyday office tasks with an intuitive, familiar interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use Excel formulas like VLOOKUP to sync data both ways?

No. Excel formulas (such as VLOOKUP, INDEX/MATCH, or FILTER) are strictly one-way. They can pull data from the main sheet to display it, but you cannot edit a formula's output cell to push data back to the original source.

Is Power Query capable of two-way synchronization?

Power Query is excellent for extracting and filtering 'unfinished' data into a new table, but it is a read-only process. If a user types over the Power Query output table, those changes will be overwritten the next time the query refreshes, rather than updating the source data.

Why do I need a unique ID for my work orders?

A unique identifier (like a specific work order number) guarantees that each row is distinct. When using a VBA macro to sync edits, the script uses this ID to find the exact match on the main sheet and apply the changes to the correct row.

Excel & Data Problems