Data Import & Export

How to Transfer PO Data and Formulas Between Excel Workbooks

Tauseeq MagsiTauseeq Magsi Nov 11, 2026 868 views

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.

How to Transfer PO Data and Formulas Between Excel Workbooks
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 you start

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.

Solution 1Recommended

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.

1
Evaluate database alternatives

Consider tools like Microsoft Access, SharePoint lists, or dedicated web apps that natively support multi-user concurrent data entry.

2
Set up auto-numbering

Configure the database primary key to automatically generate sequential PO numbers, eliminating the risk of duplicates.

3
Create user views

Build filtered views or forms so individual salespeople only see and edit their assigned purchase orders.

Transition to a Database-Backed System for Multi-User Data
Scalability: Database systems handle concurrent edits seamlessly, unlike linked Excel files which may become corrupt or locked.
Free Microsoft Office alternative

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. 1. Download and install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
  2. 2. Open your Excel workbooks: Launch WPS Spreadsheet and open your existing .xlsx PO workbooks with full format compatibility.
  3. 3. Share via WPS Cloud: Upload your central workbook to WPS Cloud and generate sharing links with specific edit permissions for your sales team.
Free and lightweight Office suiteFully compatible with Microsoft Excel (.xlsx) formatsCloud collaboration for team data sharingFamiliar interface with zero learning curve
microsoft office alternative - wps office

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.