File Sharing Problems

How to Automatically Copy Matching Rows from a Shared Excel or Google Sheet

Kushani NimanthikaKushani Nimanthika Nov 10, 2026 869 views

Question details

The user wants to automatically pull specific rows based on a condition (e.g., marked with "JC") from a shared team workbook into a personal workbook, ensuring updates in the source reflect in the destination.

How to Automatically Copy Matching Rows from a Shared Excel or Google Sheet
Product
Microsoft Excel, Google Sheets
Device & OS
not provided
Scenario
Extracting assigned tasks or specific data records from a collaborative team spreadsheet into a private tracking file.
Observed behavior
The user needs a dynamic solution where the personal workbook updates automatically when the shared team workbook changes, eliminating the need for manual copying and pasting.
Before you start

Ensure you have read access to the shared team workbook. If using Google Sheets, you will need the full URL of the source spreadsheet and must grant access permissions the first time you connect the files.

Solution 1Recommended

Use IMPORTRANGE with QUERY or FILTER in Google Sheets

Best for Google Sheets users who need real-time, one-way synchronization across web-based spreadsheets.

Google Sheets provides the IMPORTRANGE function to bring data from one URL to another. Wrapping this in a QUERY or FILTER function allows you to isolate exactly the rows you need.

1
Open the destination sheet

Open your personal Google Sheet and select the starting cell where you want the data to appear (e.g., A2).

2
Enter the QUERY formula

Type the following formula, replacing the URL and column criteria as needed: =QUERY(IMPORTRANGE("URL_of_TEAM_Workbook", "Sheet1!A:Z"), "SELECT * WHERE Col1 = 'JC'").

3
Alternative FILTER formula

If you prefer the FILTER function, use: =FILTER(IMPORTRANGE("URL", "Sheet1!A:Z"), INDEX(IMPORTRANGE("URL", "Sheet1!A:A"), 0) = "JC").

4
Allow access

Press Enter. The cell will likely display a #REF! error initially. Hover over the cell and click the 'Allow access' button to connect the two spreadsheets.

Use IMPORTRANGE with QUERY or FILTER in Google Sheets
One-Way Synchronization: This creates a one-way sync. Edits made in the team workbook will automatically appear here, but you cannot edit the imported data in the personal sheet without breaking the formula.
Advanced Spreadsheet Management

Easily Filter and Sync Data Across Workbooks with WPS Office

WPS Spreadsheet offers powerful dynamic array functions like FILTER, allowing you to seamlessly pull and synchronize data from shared team files into your personal workbooks with complete Microsoft Excel compatibility.

  1. 1. Launch WPS Spreadsheet: Open WPS Office and launch the Spreadsheet application.
  2. 2. Open your workbooks: Open both your source workbook (Team data) and your target workbook (Personal data) in separate tabs.
  3. 3. Apply the formula: Type =FILTER( in your target workbook, then switch tabs to the source workbook to highlight the data array and criteria ranges.
  4. 4. Save and sync: Press Enter and save your target workbook. The data will automatically update whenever the source file is modified and saved.
Fully compatible with Microsoft Excel formulas, including dynamic arrays like FILTER.Lightweight and fast, even when handling large datasets across multiple workbooks.Built-in cloud collaboration features for easy team sharing and real-time syncing.
microsoft office alternative - wps office

Frequently Asked Questions

Can I edit the pulled data in my personal workbook and have it update the team workbook?

No, formulas like IMPORTRANGE, QUERY, and FILTER only provide one-way synchronization (from Team to Personal). For two-way synchronization where edits in the personal sheet write back to the team sheet, you would need to use advanced programming tools like Google Apps Script, VBA macros, or Office Scripts.

Why does my Google Sheets IMPORTRANGE show a #REF! error?

This usually happens because the spreadsheets haven't been connected yet. Hover over the cell with the error and a prompt will appear. Click 'Allow access' to grant permission for the sheets to link.

Will the Excel FILTER formula work if the source workbook is closed?

Yes. Once the formula is created while both workbooks are open, Excel stores the full file path. When you close the source workbook, the formula automatically updates to include the file path and continues to fetch the latest saved data upon recalculation.

How can I copy rows matching multiple conditions?

In Excel, you can use the addition operator within the FILTER criteria for 'OR' logic: =FILTER(Range, (CriteriaRange="JC")+(CriteriaRange="AB")). In Google Sheets using QUERY, you can use standard OR logic: "SELECT * WHERE Col1 = 'JC' OR Col1 = 'AB'".