How to Transfer PO Data and Formulas Between Excel Workbooks
Question details
The user needs to copy purchase order data from a central workbook to individual salesperson workbooks while preserving formulas and unique PO numbers.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sharing and managing PO data across a team of salespeople using a centralized file system.
- Observed behavior
- Requires a reliable method to distribute data without creating duplicate PO numbers or breaking formulas in a multi-user environment.
Before setting up a multi-workbook data transfer, ensure that all participating workbooks are stored in a shared network drive or cloud folder where all users have appropriate access permissions. It is also highly recommended to create a backup of your central workbook before applying new formulas or sharing settings.
Transition to a Database-Backed System for Multi-User Data
For shared, multi-user data entry, replacing separate workbooks with a centralized database application prevents duplicate PO numbering and synchronization errors.
While Excel can link workbooks, relying on it for multi-user unique ID generation (like PO numbers) often leads to file locks and duplicate entries. A database-backed web application is more robust for routing records and ensuring data integrity.
Consider tools like Microsoft Access, SharePoint lists, or dedicated web apps that natively support multi-user concurrent data entry.
Configure the database primary key to automatically generate sequential PO numbers, eliminating the risk of duplicates.
Build filtered views or forms so individual salespeople only see and edit their assigned purchase orders.

Use Power Query to Distribute Central Workbook Data
If you must remain in Excel, use Power Query to pull data from the central workbook into individual salesperson files.
Use WPS Office for Seamless Spreadsheet Collaboration
While traditional linked workbooks can cause data conflicts, WPS Office offers cloud-based document sharing and collaboration. As a free, lightweight alternative to Microsoft Office, WPS Spreadsheet is highly compatible with Excel formats and provides an intuitive UI for data management.
- 1. Download and install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
- 2. Open your Excel workbooks: Launch WPS Spreadsheet and open your existing .xlsx PO workbooks with full format compatibility.
- 3. Share via WPS Cloud: Upload your central workbook to WPS Cloud and generate sharing links with specific edit permissions for your sales team.

Frequently Asked Questions
Can I prevent duplicate PO numbers when multiple users edit the same Excel file?
In a standard shared Excel workbook, simultaneous edits can sometimes cause conflicts leading to duplicate PO numbers. To prevent this, it is recommended to use a database application or form-based entry system that assigns IDs securely upon submission.
How do I keep formulas intact when copying data to another workbook?
To preserve formulas, you can use the 'Paste Special' > 'Formulas' option when manually copying. If automating via Power Query, formulas are evaluated in the source file and imported as values, so you will need to recreate the formulas in the destination workbook if dynamic calculation is required.
Is it better to use linked workbooks or a single shared workbook for a sales team?
A single cloud-shared workbook with appropriate sheet protections is generally more stable than multiple linked workbooks, which can suffer from broken links and synchronization delays when moved or renamed.




