How to Copy Rows with a Specific Status to an Excel Summary Sheet
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.

- 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.
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.
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.
Navigate to each job-card worksheet, highlight your data range, and press Ctrl+T to convert it into an official Excel Table.
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().
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.
Locate the Status column (e.g., Column 20 or Column T) and click the filter dropdown. Uncheck everything except 'Incomplete'.
Click Close & Load to output the consolidated list directly to your Outstanding Jobcards summary sheet.

Use VSTACK and FILTER Dynamic Array Formulas
For users with modern spreadsheet software, dynamic array formulas offer a real-time summary without the need to manually refresh queries.
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. 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. Apply the dynamic formula: Type the =VSTACK(start:end!A1:Z1000) formula in your summary sheet.
- 3. Filter the target status: Wrap the stacked array with the FILTER function to dynamically isolate the 'Incomplete' status rows, then press Enter.

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).




