How to Fix VSTACK #REF Errors When Combining Multiple Excel Sheets in WPS Office
Question details
The user needs to resolve VSTACK #REF! errors that appear when a three-dimensional reference is expanded from some worksheets to additional sheets during a multi-sheet combine operation.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Combining multiple Excel worksheets with a 3D reference such as '1:6'!F71:R79, then adding more sheets to the reference.
- Observed behavior
- The reference works for a smaller sheet range but returns #REF! when additional sheets are included because of an unsupported sheet reference, inconsistent ranges, or a problem on one specific worksheet.
Open the workbook in WPS Office Spreadsheets and confirm the sheet names, the sheet order, and the exact range F71:R79 used in the VSTACK or 3D reference. Have an empty test cell ready so you can check incremental references such as '1:6'!F71 and '1:7'!F71.
Test the 3D Reference Incrementally to Isolate the Bad Sheet
Use this solution when VSTACK works for some sheets but returns #REF! after you add more sheets; it finds the exact sheet causing the error.
A #REF! error from a 3D reference usually means one sheet in the range is missing, out of order, or does not contain the referenced range. Testing from the smallest sheet range upward reveals the first sheet that breaks the formula.
Open the workbook in WPS Office Spreadsheets and click an empty cell outside your data area.
Type =SUM('1:6'!F71) or the smallest sheet range you intend to use, then press Enter and note the result.
Change the formula to =SUM('1:7'!F71), then =SUM('1:8'!F71), continuing until the #REF! error appears.
When #REF! first appears after adding a sheet, check that the sheet exists, its name matches the reference, and the F71:R79 range is present and compatible.
Verify Sheet Names, Ranges, and Data Compatibility for VSTACK
Use this solution when the error is likely caused by inconsistent ranges, missing sheets, or mixed data types across sheets.
Use Explicit VSTACK References Instead of a 3D Reference
Use this solution when the 3D reference continues to return #REF! or when you want clearer control over the sheet order.
Combine Excel Sheets with VSTACK in WPS Office Without #REF Errors
WPS Office Spreadsheets lets you test 3D references, verify sheet ranges, and switch to explicit VSTACK formulas so you can combine multiple Excel sheets cleanly. It supports the same dynamic array behavior and saves workbooks in .xlsx format for seamless Excel compatibility.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the Excel workbook that contains the sheets you want to combine.
- 2. Test the 3D reference incrementally: In an empty cell, enter =SUM('1:6'!F71:R79), then expand to '1:7', '1:8', and so on to locate the sheet causing #REF!.
- 3. Repair the problematic sheet or range: Confirm the sheet name exists, restore the F71:R79 range, and make sure data types match across all sheets.
- 4. Enter an explicit VSTACK formula: In the output cell, type =VSTACK(Sheet1!F71:R79,Sheet2!F71:R79,...) and press Enter to stack the data.
- 5. Save in .xlsx format: Save the corrected workbook as .xlsx to preserve compatibility with Microsoft Excel and future WPS Office use.
Frequently Asked Questions
Why does VSTACK return #REF! when I use a 3D reference like '1:10'!F71:R79?
A 3D reference fails when one referenced sheet does not exist, the exact range is missing, or the data type differs across sheets. Test incrementally with =SUM('1:6'!F71), then =SUM('1:7'!F71), until the error appears.
Can I combine multiple Excel sheets with VSTACK in WPS Office?
Yes. WPS Office Spreadsheets supports VSTACK. Open your workbook, click an empty output cell, and enter =VSTACK(Sheet1!F71:R79,Sheet2!F71:R79,...) with explicit references to avoid #REF! errors.
How do I find which sheet causes the #REF! error?
Use the sheet tabs in WPS Office to verify that every sheet name exists. Then test the reference step by step; the sheet where the formula changes from a valid result to #REF! is usually the problem.
Do I have to use a 3D reference with VSTACK?
No. If a 3D reference fails, use explicit references such as =VSTACK(Sheet1!F71:R79,Sheet2!F71:R79,...). This is more stable and easier to audit.
Is this VSTACK fix compatible with Microsoft Excel?
Yes. WPS Office saves .xlsx files and supports the same VSTACK formula syntax, so corrected workbooks open normally in Microsoft Excel.
