Function Problems

How to Copy Excel Rows to Another Workbook When a Cell Equals Yes

Steve K Oct 7, 2026 868 views

Question details

The user wants to dynamically display specific columns from a source Excel workbook in a destination workbook, but only when a designated column (such as 'Add to calendar') contains the value 'Yes'. The output should be blank if there is no match.

How to Copy Excel Rows to Another Workbook When a Cell Equals Yes

Product: Excel
Device & OS: not provided
Scenario: Transferring and filtering specific tabular data across two separate Excel workbooks dynamically based on specific criteria.
Observed behavior: Specific columns from matched rows are displayed in the new workbook, and the formula gracefully returns a blank result instead of a #CALC! error when the condition is not met.

Before you start

Ensure both the source and destination workbooks are saved in the same local folder or network drive. Format your source data as an Excel Table (Ctrl + T) to ensure your formulas automatically include new rows.

Solution 1Recommended

Use FILTER and CHOOSECOLS Functions

Combine the FILTER function to find the matching rows and the CHOOSECOLS function to extract only the necessary columns into your second workbook.

The FILTER function is perfect for extracting rows based on criteria (like a cell equaling 'Yes'). By wrapping it in CHOOSECOLS, you can strip away the columns you don't need from the final output.

To prevent unsightly errors when no rows match the criteria, we will wrap the entire formula in an IFERROR function.

Format Source Data as a Table

In your source workbook, select your dataset and press Ctrl + T. Check 'My table has headers' and click OK. Name the table 'SourceTable' in the Table Design tab.

Open Both Workbooks

Open both the source workbook and the destination workbook in the same Excel instance to allow seamless formula linking without triggering external reference warnings.

Enter the Formula in Destination Workbook

In the destination workbook, select the top-left cell where you want the data to start and enter: =IFERROR(CHOOSECOLS(FILTER(SourceTable,SourceTable[Add to calendar]="Yes"),3,4,5,6),""). Modify the numbers (3,4,5,6) to match the exact column indexes you wish to pull.

Formula Linking Strategy: Using exact external table references ensures your data remains synced. Keep the source workbook accessible so the external links update correctly when opened.

WPS Spreadsheet Solutions

Filter and Sync Data Across Workbooks in WPS Office

WPS Spreadsheet seamlessly handles advanced dynamic array formulas like FILTER and CHOOSECOLS, making it incredibly easy to link and filter data across multiple workbooks automatically.

1. Prepare the Source Table: Open your source file in WPS Spreadsheet, select the data, and press Ctrl + T to create a formatted Table.
2. Link the Destination File: Open the second workbook in a new tab within WPS Office and type =FILTER( to begin writing your condition.
3. Apply Array Formula: Select the source table ranges directly with your mouse to automatically build the cross-workbook reference, finish the CHOOSECOLS structure, and press Enter.

Fully compatible with Microsoft Excel array functions and external linksProcesses heavy data and complex formulas with lightning speedCompletely free and lightweight Office alternative

Frequently Asked Questions

Why does my dynamic formula return a #REF! error?

This usually happens if the source workbook is closed, renamed, or moved. Dynamic array functions linked to external workbooks often require the source workbook to be open in the background to calculate properly.

How can I avoid the #CALC! error when no cells equal 'Yes'?

The FILTER function returns a #CALC! error if no records meet the criteria. You can fix this by wrapping your entire formula in an IFERROR function, like this: =IFERROR(FILTER(...), ""), which outputs a blank cell instead.

What if my version of Excel does not support CHOOSECOLS?

If CHOOSECOLS is unavailable, you can use the INDEX function combined with an array constant to pick specific columns. Alternatively, you can use Power Query to filter rows where the column equals 'Yes' and remove the unnecessary columns before loading it into the new workbook.

Excel & Data Problems