This is a frustrating bug that Excel users should not have to accept as normal. The user built a clean, modern workflow, Power Query for data loading, dynamic arrays for calculations, named ranges for flexibility, and Excel's charting engine simply refused to cooperate. The named range works perfectly in the worksheet. It spills correctly. It returns clean numeric data. Excel even highlights the intended range when you press F3. Then, in the chart series editor, it throws a generic formula error and blocks the commit. That is not a user mistake. It is a product limitation that breaks a legitimate, well-structured use case.
What this means in practice is that anyone trying to build adaptive dashboards with modern Excel features hits a wall. The user's approach is exactly what we would recommend: use named ranges to decouple chart references from fixed cell addresses, so that when dynamic arrays resize after a data refresh, the chart follows along. But Excel's chart engine still expects a static reference under the hood. It converts spill ranges to fixed ranges or rejects named range formulas outright. So the chart either breaks on refresh or never accepts the formula in the first place. That forces users to choose between dynamic calculations and reliable charts. In a reporting workbook, that is not a choice at all.
The error message is especially telling. It tells the user to check parentheses and arguments, as if the formula itself were malformed. But the formula is valid everywhere else in the workbook. The problem is not the syntax. The problem is that the charting engine has not been updated to handle the same dynamic references that the worksheet handles natively. Excel's calculation engine and its charting engine are out of sync. That is a design debt that Microsoft needs to address, not a quirk users should work around with INDIRECT or INDEX hacks that eventually fail anyway.
The concrete takeaway is this: if you rely on Power Query and dynamic arrays for automated reporting, do not assume charts will work the same way the rest of your workbook does. Test your chart references after every data refresh, and be prepared to rebuild them manually when array sizes change. That is not a scalable workflow, and it is not fair to the people who invest time in building these systems. Excel's chart engine needs to catch up to the rest of the product. Until it does, the most reliable solution may be to export chart data to a static range before linking it to a chart, a step backward that defeats the purpose of dynamic arrays in the first place.