How to Find the Parent Cell of a Dynamic Array Spill in WPS Spreadsheets
Question details
Identify the top-left cell that creates a dynamic array spill range in a spreadsheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with dynamic array formulas that spill into multiple cells and needs to locate the originating parent cell.
- Observed behavior
- A spilled dynamic array shows results in adjacent cells, but worksheet formulas do not provide a direct parent-cell function; VBA can return the parent cell with SpillParent.
Make sure the workbook contains dynamic array formulas and that you can see the spill range. If you plan to use VBA, enable the Developer tab in WPS Spreadsheets before opening the editor.
Use VBA and the SpillParent Property to Return the Spill Parent Cell
Best for reliable identification when you frequently work with dynamic array spills and macros are available.
The VBA Range object exposes a SpillParent property that points to the cell containing the dynamic array formula. This avoids fragile worksheet workarounds.
In WPS Spreadsheets, press Alt+F11 or go to the Developer tab and click Visual Basic to open the VBA editor.
In the Project Explorer, right-click your workbook, choose Insert > Module, and open the new module window.
Paste the following VBA function: Function SpillParentCell(rng As Range) As Range: If rng.HasSpill Then Set SpillParentCell = rng.SpillParent Else Set SpillParentCell = rng: End If: End Function. Then return to the worksheet and use =SpillParentCell(B5) in a blank cell.
Click a cell inside the spill and enter =SpillParentCell(cell) in a blank cell to display the parent cell address.
Use a Formula Workaround with INDIRECT and the # Spill Operator
Use when macros are not available and you can accept a limited, formula-based check.
Find Dynamic Array Spill Parents Directly in WPS Spreadsheets
WPS Spreadsheets supports dynamic array formulas and VBA, so you can identify the spill parent cell without switching office suites. Use the built-in spill references or a small VBA function to locate the parent cell and keep your workbook manageable.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains the dynamic array formulas, and click any cell in the spill range.
- 2. Confirm the spill range: Look for the spill border around the results; the top-left cell inside that border is the parent cell you are looking for.
- 3. Use a VBA helper function: Press Alt+F11, insert a module, and add the SpillParent function to return the parent cell address for any selected spill cell.
- 4. Save the workbook: Save the file as .xlsx or .xlsm to keep formulas and macros compatible with WPS Spreadsheets and Microsoft Excel.
Frequently Asked Questions
How can I find the parent cell of a dynamic array spill without VBA?
You can use a formula workaround with INDIRECT and the # spill operator, but it only checks candidate cells and can be fragile. For example, test references like A2# with ISREF(INDIRECT("A2#")) and return the text before # when the reference is valid.
Does WPS Spreadsheets support dynamic array spills?
Yes, WPS Spreadsheets supports dynamic array formulas and spill ranges. You can enter formulas such as FILTER, SORT, or SEQUENCE, and the results spill into adjacent cells when space is available.
What is the SpillParent property?
SpillParent is a VBA Range property that returns the top-left cell containing the formula that created a spill. It is available when the range has a spill, often checked with Range.HasSpill.
Why does my formula workaround return a #REF! error?
The INDIRECT and ISREF method depends on a valid spill reference ending in #. If the reference is not a spill range, the sheet is protected, or the candidate address is wrong, the formula may return #REF! or an empty result.
Can I edit the spill parent cell after locating it?
Yes, the parent cell is the only cell that contains the dynamic array formula. You can select the parent cell and edit the formula; the entire spill range will update automatically.
