Function Problems

How to Copy Excel Rows to Another Sheet Based on a Dropdown Value

Maira Mehtab Sep 20, 2026 869 views

Question details

The user wants to automatically display specific columns (B through F) on a destination sheet when a Yes/No dropdown in column A of the source sheet is set to 'Yes'.

Product: Excel
Device & OS: not provided
Scenario: Filtering and copying data records dynamically across multiple sheets based on user selections in a dropdown list.
Observed behavior: The data needs to be transferred dynamically based on the dropdown condition, but existing merged rows in the records prevent dynamic array formulas from executing properly.

Before you start

Ensure your source and destination ranges do not contain merged cells. Merged cells block dynamic array formulas like FILTER from spilling correctly into the adjacent rows.

Solution 1Recommended

Use the FILTER Function to Extract Data Dynamically

The FILTER function is the most efficient way to automatically pull rows to another sheet based on a specific condition, such as a dropdown being set to 'Yes'.

Before applying the formula, it is crucial to unmerge any cells in both the source and target sheets. Merged cells will cause a #SPILL! error because they prevent the formula results from expanding into multiple rows.

Unmerge destination cells

Navigate to your target sheet, select any merged cells where the data will appear, and click 'Unmerge Cells' located in the Alignment group of the Home tab.

Select the target cell

Click on the top-left cell of the destination sheet where you want the filtered data columns to start populating.

Enter the FILTER formula

Type the formula =FILTER(Sheet2!B2:F51, Sheet2!A2:A51="Yes", "") into the formula bar. Be sure to replace 'Sheet2' with the actual name of your source sheet.

Execute the formula

Press Enter. The formula will automatically spill down and across to display all columns (B through F) for records where column A equals 'Yes'.

Understanding the Formula Syntax: The first argument (Sheet2!B2:F51) is the data range to return. The second argument (Sheet2!A2:A51="Yes") is the condition it checks. The third argument ("") tells the spreadsheet to return a blank cell if no records match the criteria.

Organize Data Efficiently

Filter and Manage Data Dynamically with WPS Office

WPS Spreadsheet fully supports dynamic array formulas like FILTER, allowing you to seamlessly pull data across sheets based on dropdown selections. It is a lightweight, feature-rich alternative that simplifies data management.

1. Open Your Workbook in WPS: Launch WPS Office and open your spreadsheet file containing the dropdown lists and raw data.
2. Unmerge Target Cells: Highlight any merged cells in your destination area, go to the Home tab, and select 'Unmerge Cells' to prepare for the dynamic array.
3. Apply the FILTER Formula: Type =FILTER(Sheet2!B2:F51, Sheet2!A2:A51="Yes", "") in your target cell and press Enter to instantly extract the matching rows.

Fully compatible with Microsoft Excel formulas (.xlsx)Supports dynamic arrays and advanced functions like FILTER without lagFree, lightweight, and fast to load for quick data analysisIntuitive interface familiar to Office users for a seamless transition

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #SPILL! error when using the FILTER function?

A #SPILL! error occurs when the destination range has existing data or merged cells blocking the formula from expanding automatically. Clear any text in the destination area and unmerge all affected cells to resolve the issue.

Can I copy rows based on multiple dropdown conditions?

Yes, you can use the asterisk (*) to multiply conditions (creating an AND logic) within the FILTER function. For example: =FILTER(B2:F51, (A2:A51="Yes")*(C2:C51="Completed"), "").

How do I create a Yes/No dropdown list in my spreadsheet?

Select the cells you want to add the dropdown to, go to Data > Data Validation, choose 'List' under the Allow criteria, and type 'Yes,No' in the Source box.

What happens if there are no 'Yes' values in the dropdown column?

If you included the third argument ("") in your FILTER formula, the cell will simply appear blank. If you omit the third argument, it will return a #CALC! error.

Excel & Data Problems