Hi everyone,

I’m running into what looks like a limitation (or bug?) in Excel when combining:

• Power Query (OData source)
• Dynamic arrays (LET, FILTER, etc.)
• Named ranges
• Charts

Setup

I have a fairly complex reporting workbook:

• Data is loaded via Power Query (OData).
• When I load a new data, a lot of calculations update.
• I heavily use dynamic arrays with LET().
• My charts are supposed to adapt dynamically to filtered data and to changes in array length.

Filtering my data works perfectly:

• The dynamic arrays resize.
• The charts update correctly.

However, when I load new data the array size sometimes changes (shorter/longer), and Excel “breaks” the chart references.

The Core Problem

If I point the chart directly to a spill range, Excel either:

• Converts it to a fixed range (e.g. $AF$37:$AF$66), or
• Refuses the formula entirely.

So I tried to solve it properly using the Name Manager.

I tried multiple approaches to ensure the named range itself is not the issue.

This name returns a clean, single-column numeric array.

• No empty strings ("")
• No errors
• No text values
• Pure numeric output
• Spills correctly in worksheet cells
• Fully usable in normal formulas

When editing the chart series:

• I press F3
• Select the named range
• Excel correctly highlights the intended range in the sheet

Everything appears valid.
However, when I confirm with OK Excel throws a formula error (see Screenshot):

Error Message (English Translation )

Here is the error Excel shows (screenshot is attached):

The formula you typed contains an error.

• Check that all required parentheses and arguments are present.
• Check that all references to other sheets or workbooks are valid.
• If you are not trying to enter a formula, avoid starting the text with an equal sign (=) or minus sign (-).

I tried to built the named ranges with INDEX() and INDIRECT() - both worked in the worksheet but not in the chart (same error message).

Additional Notes

• The named ranges are defined at Workbook scope
• No spaces or invalid characters in the names
• No blanks, no empty strings
• No visible errors
• The issue occurs only inside the chart series editor
• Creating the chart directly on the same sheet does not change the behavior
• workbook is in .xlsm

The named range is clearly recognized and highlighted by Excel — it just cannot be committed in the chart.