Power Query Problems

How to Copy Rows with a Specific Status to an Excel Summary Sheet

Bushra ParveenBushra Parveen Nov 10, 2026 869 views

Question details

The user needs to collect rows marked as "Incomplete" from multiple job-card worksheets and display them dynamically on an Outstanding Jobcards summary sheet.

How to Copy Rows with a Specific Status to an Excel Summary Sheet
Product
Excel
Device & OS
not provided
Scenario
Managing multiple job-card worksheets and needing a consolidated view of outstanding tasks without copying and pasting manually.
Observed behavior
The user is looking for an automated way to combine data across sheets and filter by a specific column status, ensuring a clean summary page.
Before you start

Ensure all your job-card worksheets follow a consistent structure, with the status column located in the exact same position (e.g., Column T) across all sheets.

Solution 1Recommended

Consolidate Data Using Power Query

Power Query is the most robust method for combining multiple worksheets, appending worksheet names, and filtering specific rows.

By utilizing Power Query, you can append data from all sheets in your workbook, extract the source sheet name, and selectively load only the rows that meet your criteria.

1
Convert data to Excel Tables

Navigate to each job-card worksheet, highlight your data range, and press Ctrl+T to convert it into an official Excel Table.

2
Load data into Power Query

Go to Data > Get Data > From File > From Workbook to import your current file, or use Get Data > From Other Sources > Blank Query and enter =Excel.CurrentWorkbook().

3
Expand and add worksheet names

Expand the combined tables to reveal the data. Add a custom column or utilize the default 'Name' column generated by Power Query to identify the source worksheet.

4
Filter by status

Locate the Status column (e.g., Column 20 or Column T) and click the filter dropdown. Uncheck everything except 'Incomplete'.

5
Load to summary sheet

Click Close & Load to output the consolidated list directly to your Outstanding Jobcards summary sheet.

Consolidate Data Using Power Query
Exclude Closed Sheets: In the Power Query Editor, you can filter the worksheet-name column to show only names containing 'ACTIVE' to automatically exclude closed jobs.
WPS Data Consolidation

Easily Combine and Filter Data Across Sheets in WPS Spreadsheet

You can achieve identical results in WPS Spreadsheet using its advanced dynamic array functions and intuitive data processing tools, allowing you to quickly summarize outstanding tasks across multiple tabs.

  1. 1. Prepare your workspace: Open your workbook in WPS Spreadsheet and set up a 'start' and 'end' sheet to frame your target job-card sheets.
  2. 2. Apply the dynamic formula: Type the =VSTACK(start:end!A1:Z1000) formula in your summary sheet.
  3. 3. Filter the target status: Wrap the stacked array with the FILTER function to dynamically isolate the 'Incomplete' status rows, then press Enter.
Fully compatible with Microsoft Excel formulas and array functionsSupports dynamic arrays like VSTACK, FILTER, and CHOOSECOLS out of the boxTabbed interface makes managing multiple job-card sheets seamlessLightweight, fast, and highly efficient for handling large datasets
microsoft office alternative - wps office

Frequently Asked Questions

How do I exclude closed worksheets from the summary?

If you are using Power Query, you can apply a text filter to the worksheet-name column to only include names containing 'ACTIVE' or simply uncheck any sheet names ending in '-CLOSED'.

Will my dynamic array formula update automatically?

Yes, dynamic array formulas like VSTACK and FILTER recalculate in real-time as data changes in the source sheets. Conversely, Power Query requires you to click Data > Refresh All to pull in the latest changes.

Can I use the formula method to copy rows to an entirely different workbook?

The VSTACK 3D reference method only works across sheets within the same open workbook. If you need to consolidate data from external workbooks, Power Query is the required tool (Get Data > From File > From Workbook).