{"record":{"id":"ae958038653f314b","repo":"iOfficeAI/OfficeCLI","slug":"ref-or-refersto-formula-property-is-requ","errorCode":null,"errorMessage":"'ref' (or 'refersTo' / 'formula') property is required for namedrange","messagePattern":"'ref' \\(or 'refersTo' / 'formula'\\) property is required for namedrange","errorType":"exception","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs","lineNumber":79,"sourceCode":"            throw new ArgumentException($\"Invalid defined-name '{nrName}': name parses as a cell reference; choose a different name.\");\n        // R39-5: Excel reserves the single letters R and C (case-insensitive)\n        // because they collide with R1C1 reference notation. Excel rejects\n        // the file with 0x800A03EC if either is used as a defined name.\n        if (nrName.Length == 1 && (nrName[0] == 'R' || nrName[0] == 'r' || nrName[0] == 'C' || nrName[0] == 'c'))\n            throw new ArgumentException($\"Invalid defined-name '{nrName}': single letter 'R' / 'C' is reserved by Excel for R1C1 reference notation; choose a different name.\");\n        // `refersTo` is the common Excel-documented alias for `ref`;\n        // silently map it so users don't end up with an empty\n        // <x:definedName/> that corrupts the file.\n        var refVal = properties.GetValueOrDefault(\"ref\",\n            properties.GetValueOrDefault(\"refersTo\",\n                properties.GetValueOrDefault(\"formula\", \"\")));\n        // R15/bt-2: reject up-front when the required ref/refersTo/formula\n        // value is missing so an empty <x:definedName/> never gets written\n        // (the resulting zombie polluted the workbook and broke later Set\n        // calls). Unsupported aliases like `range=` previously silently\n        // landed here as empty and produced the zombie.\n        if (string.IsNullOrEmpty(refVal))\n            throw new ArgumentException(\"'ref' (or 'refersTo' / 'formula') property is required for namedrange\");\n        // R7-2: per ECMA-376 §18.2.5, <x:definedName> content must NOT\n        // have a leading '=' (unlike the formula-bar form in Excel UI).\n        // Excel rejects the file with 0x800A03EC if '=' is present.\n        if (refVal.StartsWith('='))\n            refVal = refVal.TrimStart('=');\n\n        // R27-1: cross-workbook references like \"[Other.xlsx]Sheet1!$A$1\"\n        // or \"[1]Sheet1!$A$1\" need an externalReferences part to resolve.\n        // Without one, Excel opens the file but formulas referencing the\n        // name show #REF!. Reject up-front rather than write a silently\n        // broken defined name.\n        // CONSISTENCY(xref-detect): bt-5/fuzz-NR01 — also catch the\n        // single-quoted form `'[Book.xlsx]Sheet'!A1` (Excel's standard\n        // quoting for sheet names with spaces) which previously slipped\n        // through and produced a silently broken defined name.\n        // CONSISTENCY(cross-workbook-vs-structured-ref): mirror Helpers.cs\n        // RejectCrossWorkbookFormula. Tight match against `[<digits>]` or\n        // `[<name>.xls(x|m|b)?]` so structured-ref namedrange values like","sourceCodeStart":61,"sourceCodeEnd":97,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs#L61-L97","documentation":"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.","triggerScenarios":"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.","commonSituations":"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=`.","solutions":["Add `--prop ref=Sheet1!A1:B1` (the canonical key).","Alternatively use `--prop refersTo=Sheet1!A1:B1` or `--prop formula=SUM(Sheet1!A1:A10)`."],"exampleFix":"// before\nadd ./book.xlsx /namedrange --type namedrange --prop name=SalesTotal --prop range=Sheet1!A1:B1\n// after\nadd ./book.xlsx /namedrange --type namedrange --prop name=SalesTotal --prop ref=Sheet1!A1:B1","handlingStrategy":"validation","validationCode":"var refVal = props.GetValueOrDefault(\"ref\")\n    ?? props.GetValueOrDefault(\"refersTo\")\n    ?? props.GetValueOrDefault(\"formula\");\nif (string.IsNullOrEmpty(refVal))\n    throw new InvalidOperationException(\"namedrange requires ref= / refersTo= / formula=\");","typeGuard":"static bool HasNamedRangeRef(IReadOnlyDictionary<string,string> p)\n    => new[]{\"ref\",\"refersTo\",\"formula\"}.Any(k => !string.IsNullOrEmpty(p.GetValueOrDefault(k)));","tryCatchPattern":"try { handler.AddNamedRange(...); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"'ref'\"))\n{ /* prompt for the reference or formula */ }","preventionTips":["Use the canonical key ref=; refersTo/formula are aliases.","Do not use range=/value=/address= — they are NOT recognized for named ranges."],"tags":["excel","named-range","defined-name","missing-property","input-validation"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}