Function Problems

Reference a Named Dynamic Array Formula in Excel and VBA

John WilsonJohn Wilson Nov 18, 2026 870 views

Question details

Reference an Excel named formula that returns a dynamic array from a worksheet formula or VBA.

Reference a Named Dynamic Array Formula in Excel and VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A name defined in Name Manager refers to a formula that produces multiple values rather than to a traditional cell range.
Observed behavior
The named formula can be evaluated in a worksheet, where its results spill into cells, or evaluated from VBA for processing.
Before you start

Use an Excel version that supports dynamic arrays, LET, and SEQUENCE, such as Excel for Microsoft 365 or Excel 2021. Check the name and its scope under Formulas > Name Manager, and make sure the intended spill area is empty.

Solution 1Recommended

Use the named formula in a worksheet

Enter the defined name as a formula to display its dynamic array, then reference the spill from its anchor cell.

1
Define the name

Open Formulas > Name Manager > New. Enter myNewList as the name and =LET(n,10,i,SEQUENCE(n),i) in Refers to, then select OK.

2
Enter the named formula

Select cell A1 on a worksheet where the name is in scope, enter =myNewList, and press Enter. The values 1 through 10 should spill down the column.

3
Reference the spilled cells

In another formula, use =A1# to refer to the full spill anchored at A1. Use the anchor cell with # rather than assuming the named formula itself is a range reference.

Use the named formula in a worksheet
Check for blocked spills: If Excel displays #SPILL!, clear any occupied cells in the intended output area and check the error indicator for other causes.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open Excel files and continue working with minimal disruption, while checking advanced formulas and macros for compatibility.

Open and edit Microsoft Excel XLSX and XLS filesWork with spreadsheet formulas and named ranges in a familiar interfaceMove existing Office documents into a lightweight, free office suiteCheck dynamic array formulas and VBA features when migrating, as support can vary
microsoft office alternative - wps office

Frequently Asked Questions

Can I enter a named formula directly in a worksheet?

Yes. If myNewList is defined in Formulas > Name Manager and is in scope, enter =myNewList in a cell. A dynamic array result can spill into neighboring cells.

How do I refer to all the cells in the spill?

Use the spill anchor followed by #. For example, if A1 contains =myNewList, enter =A1# in another formula to reference its spilled result.

Why does the formula show #SPILL!?

The intended output area may be blocked or otherwise unavailable. Select the error indicator in Excel, inspect the reported cause, and clear any obstructing cells.

Why doesn't VBA Range("myNewList") return the array?

The name may refer to a formula rather than a range. Use the appropriate worksheet's Evaluate("myNewList") and check whether the result is an array or an error.

Does the name's scope matter?

Yes. Check the Scope column in Formulas > Name Manager. A worksheet-scoped name must be used or evaluated in the appropriate worksheet context.