How to Pull Rows to Another Sheet Based on Quantity in Excel & WPS
Question details
The user wants to create an automated pick list on a separate sheet by extracting only the rows where the quantity column contains a numerical value.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Filtering an inventory or order list dynamically to show only items that have a recorded quantity on a new sheet.
- Observed behavior
- Needs a formula or dynamic method to extract specific rows to a destination sheet without relying on manual copying and pasting.
Ensure your source data is formatted as a clean table without merged cells. Identify the exact range of your data and the specific column that holds your quantity values.
Use the FILTER Function (Recommended)
The fastest and most dynamic way to pull rows based on conditions in modern spreadsheet software.
The FILTER function is a dynamic array formula available in Office 365, Excel 2021, and recent versions of WPS Spreadsheet. It automatically spills results into adjacent cells and updates instantly when source data changes.
Go to the new sheet where you want your pick list to appear and click on the top-left cell where the data should start.
Type the formula: =FILTER(A2:F100, ISNUMBER(F2:F100), "No items required"). Adjust A2:F100 to match your entire source data range, and F2:F100 to match your quantity column.
Press Enter. The formula will evaluate the quantity column and dynamically spill all rows containing a number into the new sheet.
Use Power Query
Ideal for larger datasets or older software versions where you need to extract and transform rows without writing complex formulas.
Use INDEX and MATCH (For Older Versions)
A backward-compatible formula solution for older spreadsheet software that does not support the FILTER dynamic array.
Use WPS Spreadsheet to Filter Data Effortlessly
WPS Spreadsheet fully supports modern dynamic array formulas, including the FILTER function. You can easily pull rows to another sheet, organize pick lists, and manage large datasets completely free of charge.
- 1. Open Your File: Launch WPS Spreadsheet and open your existing workbook containing the inventory or source data.
- 2. Apply the Formula: Navigate to the target sheet, click your starting cell, and enter the =FILTER() formula referencing your quantity conditions.
- 3. Get Instant Results: Press Enter to instantly populate and dynamically link your filtered rows.
Frequently Asked Questions
Why does my FILTER function return a #SPILL! error?
A #SPILL! error means there is not enough empty space on your destination sheet for the formula to output the filtered rows. Look for any text or spaces in the adjacent cells and clear them to allow the formula to expand.
What if no rows meet the quantity condition?
If no rows contain a quantity, the formula will return a #CALC! error. You can prevent this by using the optional [if_empty] argument at the end of your formula, for example: =FILTER(A2:F100, ISNUMBER(F2:F100), "No items required").
Can I filter rows based on conditions in multiple columns?
Yes. You can combine multiple criteria by multiplying them. For example, to pull rows where the quantity is a number AND the status is 'In Stock', use =FILTER(A2:F100, (ISNUMBER(F2:F100))*(B2:B100="In Stock")).
How do I extract only specific columns instead of the entire row?
You can wrap your FILTER function inside a CHOOSECOLS function. For example, =CHOOSECOLS(FILTER(A2:F100, ISNUMBER(F2:F100)), 1, 3, 6) will pull only the 1st, 3rd, and 6th columns of the matching rows.
