WPS Spreadsheet: Fix INDIRECT #SPILL! Errors Across Multiple Sheets
Question details
Retrieve values from the same range across multiple worksheets without generating #SPILL! errors or FALSE results when the formula is filled down.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A workbook uses worksheet names and INDIRECT to collect values from range B1:B15 on multiple sheets.
- Observed behavior
- Referencing an entire range inside INDIRECT produces a #SPILL! error, while filling the formula down can return FALSE or incorrect results.
Check that every worksheet name is spelled exactly as it appears on the sheet tab and that the source cells use the same range, such as B1:B15. Clear any content below the destination formula so a dynamic array has room to expand.
Return one source cell per row with INDIRECT
Use this approach when the result should contain one value on each row instead of spilling an entire range from every worksheet.
A #SPILL! error occurs when INDIRECT returns a multi-cell range in a position where the formula is expected to produce one value. Build the reference to one cell at a time by combining the sheet name with the required row number.
In WPS Spreadsheet, click the first cell where the retrieved value should appear, such as C16.
Enter =VALUE(INDIRECT("'"&OFFSET(B16,-MOD(ROW()-1,15),0)&"'!B"&MOD(ROW()-1,15)+1)) and press Enter. This constructs one worksheet-and-cell reference instead of passing B1:B15 as one spilled range.
Drag the fill handle from the first result cell down, or copy the formula and use Home > Paste. Confirm that each 15-row block points to B1:B15 on the intended worksheet.
If a result is FALSE or an error remains, verify the sheet name in column B, the source row number, and whether the referenced source cell contains a logical value.
Stack the same range from multiple sheets with VSTACK and REDUCE
Use this option in a version that supports dynamic-array functions when you want one combined list from B1:B15 on every worksheet.
Use WPS Spreadsheet to retrieve matching values across worksheets
WPS Spreadsheet provides an Excel-compatible workspace for building and filling INDIRECT formulas, checking dynamic-array results, and combining values from multiple sheets with familiar spreadsheet tools.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the worksheet names and source ranges.
- 2. Create the formula: Select the output cell and enter the single-cell INDIRECT formula, or use the VSTACK and REDUCE formula if your version supports dynamic arrays.
- 3. Fill or spill the results: Use the fill handle for the row-by-row formula, or leave the dynamic-array output area empty so WPS Spreadsheet can spill the combined results.
- 4. Save the corrected workbook: Choose Menu > Save As and select XLSX if the workbook will be opened in Microsoft Excel.
Frequently Asked Questions
Why does INDIRECT return a #SPILL! error when I reference B1:B15?
INDIRECT returns the entire B1:B15 range as an array, so the result needs multiple empty cells. Use a row-specific reference such as INDIRECT("'"&B16&"'!B1") or the provided MOD-based formula when you need one value per row.
How can I stop FALSE values from appearing when I fill the formula down?
Check that the formula builds the correct row number and sheet name for each filled row. Wrapping the single-cell reference in VALUE can convert numeric text, while incorrect or blank sheet-name cells should be corrected in the helper column.
Can I combine B1:B15 from several worksheets into one list?
Yes. In a version that supports dynamic arrays, list the sheet names in one column and use LET with INDIRECT, VSTACK, and REDUCE to append B1:B15 from each sheet. Keep the destination spill area empty.
What should I do if VSTACK or REDUCE is unavailable?
Use the row-by-row INDIRECT formula instead, or update WPS Office to a version that supports those dynamic-array functions. You can also retrieve each worksheet range in separate sections and combine the results manually.
Does the worksheet name need quotation marks in an INDIRECT reference?
Use single quotation marks around the sheet name, as in INDIRECT("'"&B16&"'!B1"). They are especially important when a worksheet name contains spaces or special characters.
