How to Automatically Copy Matching Rows from a Shared Excel or Google Sheet
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.

- 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.
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.
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.
Open your personal Google Sheet and select the starting cell where you want the data to appear (e.g., A2).
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'").
If you prefer the FILTER function, use: =FILTER(IMPORTRANGE("URL", "Sheet1!A:Z"), INDEX(IMPORTRANGE("URL", "Sheet1!A:A"), 0) = "JC").
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 the FILTER Function Across Workbooks in Excel
Ideal for Excel users working with desktop files saved locally or on a shared network drive.
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. Launch WPS Spreadsheet: Open WPS Office and launch the Spreadsheet application.
- 2. Open your workbooks: Open both your source workbook (Team data) and your target workbook (Personal data) in separate tabs.
- 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. Save and sync: Press Enter and save your target workbook. The data will automatically update whenever the source file is modified and saved.

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'".




