iOfficeAI/OfficeCLI · error · ArgumentException

'ref' (or 'refersTo' / 'formula') property is required for n

Error message

'ref' (or 'refersTo' / 'formula') property is required for namedrange

What it means

Thrown when no reference/formula value can be resolved for the named range. The value is looked up under `ref`, then alias `refersTo`, then `formula` (defaulting to empty). If the result is null/empty the throw fires. This guard prevents an empty <x:definedName/> from being written, which previously polluted the workbook and broke later Set calls. Note that unsupported aliases like `range=` do NOT satisfy this — only ref/refersTo/formula are recognized.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs:79

            throw new ArgumentException($"Invalid defined-name '{nrName}': name parses as a cell reference; choose a different name.");
        // R39-5: Excel reserves the single letters R and C (case-insensitive)
        // because they collide with R1C1 reference notation. Excel rejects
        // the file with 0x800A03EC if either is used as a defined name.
        if (nrName.Length == 1 && (nrName[0] == 'R' || nrName[0] == 'r' || nrName[0] == 'C' || nrName[0] == 'c'))
            throw new ArgumentException($"Invalid defined-name '{nrName}': single letter 'R' / 'C' is reserved by Excel for R1C1 reference notation; choose a different name.");
        // `refersTo` is the common Excel-documented alias for `ref`;
        // silently map it so users don't end up with an empty
        // <x:definedName/> that corrupts the file.
        var refVal = properties.GetValueOrDefault("ref",
            properties.GetValueOrDefault("refersTo",
                properties.GetValueOrDefault("formula", "")));
        // R15/bt-2: reject up-front when the required ref/refersTo/formula
        // value is missing so an empty <x:definedName/> never gets written
        // (the resulting zombie polluted the workbook and broke later Set
        // calls). Unsupported aliases like `range=` previously silently
        // landed here as empty and produced the zombie.
        if (string.IsNullOrEmpty(refVal))
            throw new ArgumentException("'ref' (or 'refersTo' / 'formula') property is required for namedrange");
        // R7-2: per ECMA-376 §18.2.5, <x:definedName> content must NOT
        // have a leading '=' (unlike the formula-bar form in Excel UI).
        // Excel rejects the file with 0x800A03EC if '=' is present.
        if (refVal.StartsWith('='))
            refVal = refVal.TrimStart('=');

        // R27-1: cross-workbook references like "[Other.xlsx]Sheet1!$A$1"
        // or "[1]Sheet1!$A$1" need an externalReferences part to resolve.
        // Without one, Excel opens the file but formulas referencing the
        // name show #REF!. Reject up-front rather than write a silently
        // broken defined name.
        // CONSISTENCY(xref-detect): bt-5/fuzz-NR01 — also catch the
        // single-quoted form `'[Book.xlsx]Sheet'!A1` (Excel's standard
        // quoting for sheet names with spaces) which previously slipped
        // through and produced a silently broken defined name.
        // CONSISTENCY(cross-workbook-vs-structured-ref): mirror Helpers.cs
        // RejectCrossWorkbookFormula. Tight match against `[<digits>]` or
        // `[<name>.xls(x|m|b)?]` so structured-ref namedrange values like

View on GitHub (pinned to 1ced45e900)

Solutions

1. Add `--prop ref=Sheet1!A1:B1` (the canonical key).
2. Alternatively use `--prop refersTo=Sheet1!A1:B1` or `--prop formula=SUM(Sheet1!A1:A10)`.

Example fix

// before
add ./book.xlsx /namedrange --type namedrange --prop name=SalesTotal --prop range=Sheet1!A1:B1
// after
add ./book.xlsx /namedrange --type namedrange --prop name=SalesTotal --prop ref=Sheet1!A1:B1

var refVal = props.GetValueOrDefault("ref")
    ?? props.GetValueOrDefault("refersTo")
    ?? props.GetValueOrDefault("formula");
if (string.IsNullOrEmpty(refVal))
    throw new InvalidOperationException("namedrange requires ref= / refersTo= / formula=");

static bool HasNamedRangeRef(IReadOnlyDictionary<string,string> p)
    => new[]{"ref","refersTo","formula"}.Any(k => !string.IsNullOrEmpty(p.GetValueOrDefault(k)));

try { handler.AddNamedRange(...); }
catch (ArgumentException ex) when (ex.Message.Contains("'ref'"))
{ /* prompt for the reference or formula */ }

When it happens

Trigger: Calling `add /namedrange --type namedrange --prop name=X` with no ref/refersTo/formula; or using an unsupported alias like `range=` or `value=` or `address=`. The canonical key is `ref`; `refersTo` and `formula` are aliases.

Common situations: Assuming `range=` is the data-range alias (it is not — for named ranges only ref/refersTo/formula count); forgetting the reference entirely; a typo like `refto=`.

Related errors

• 'name' property is required for namedrange
• Invalid defined-name '{nrName}': must start with a letter/un
• Invalid defined-name '{nrName}': length {nrName.Length} exce
• Invalid defined-name '{nrName}': name parses as a cell refer
• Invalid defined-name '{nrName}': single letter 'R' / 'C' is

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/ae958038653f314b. Report an issue: GitHub.