{"record":{"id":"e00a0b71335a70fc","repo":"iOfficeAI/OfficeCLI","slug":"defined-name-ref-refval-has-no-sheet-qualifier","errorCode":null,"errorMessage":"Defined-name ref '{refVal}' has no sheet qualifier — Excel refuses unqualified cell ranges in defined names. Use ref=SheetName!A1:B1, or add via the sheet path (add <file> /Sheet1 --type namedrange ...).","messagePattern":"Defined-name ref '(.+?)' has no sheet qualifier — Excel refuses unqualified cell ranges in defined names\\. Use ref=SheetName!A1:B1, or add via the sheet path \\(add <file> /Sheet1 --type namedrange \\.\\.\\.\\)\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs","lineNumber":127,"sourceCode":"        // A bare A1-style range with no sheet qualifier is also INVALID in a\n        // defined-name body — Excel refuses the whole file (0x800A03EC).\n        // When the parent path names a sheet (add /Sheet1 --type namedrange),\n        // qualify with it; otherwise the ref is ambiguous and rejected.\n        if (!refVal.Contains('!')\n            && System.Text.RegularExpressions.Regex.IsMatch(refVal.Replace(\"$\", \"\"),\n                @\"^[A-Za-z]{1,3}\\d+(:[A-Za-z]{1,3}\\d+)?$\"))\n        {\n            var nrParentSheet = parentPath.TrimStart('/').Split('/', 2)[0];\n            if (!string.IsNullOrEmpty(nrParentSheet)\n                && !nrParentSheet.StartsWith(\"namedrange\", StringComparison.OrdinalIgnoreCase)\n                && !nrParentSheet.Equals(\"workbook\", StringComparison.OrdinalIgnoreCase)\n                && FindWorksheet(nrParentSheet) != null)\n            {\n                refVal = $\"{Core.ModernFunctionQualifier.QuoteSheetNameForRef(nrParentSheet)}!{refVal}\";\n            }\n            else\n            {\n                throw new ArgumentException(\n                    $\"Defined-name ref '{refVal}' has no sheet qualifier — Excel refuses unqualified cell ranges \" +\n                    \"in defined names. Use ref=SheetName!A1:B1, or add via the sheet path (add <file> /Sheet1 --type namedrange ...).\");\n            }\n        }\n        ValidateDefinedNameRef(refVal);\n\n        var workbook = GetWorkbook();\n        // CONSISTENCY(workbook-child-order): helper inserts <definedNames>\n        // in schema-correct position (before calcPr/oleSize/...).\n        var definedNames = GetOrCreateDefinedNames(workbook);\n\n        var dn = new DefinedName(refVal) { Name = nrName };\n\n        // Scope resolution: explicit --prop scope= wins. Otherwise, if the\n        // parentPath addresses a specific sheet (e.g. \"/Sheet1\"), default\n        // the scope to that sheet — callers who picked a sheet-level\n        // parent almost always wanted a sheet-scoped name, and previously\n        // the parentPath was silently ignored and the name landed at","sourceCodeStart":109,"sourceCodeEnd":145,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs#L109-L145","documentation":"Thrown when the ref value is a bare A1-style range with NO sheet qualifier (`!`) AND the parent path does not name a valid sheet to auto-qualify with. Excel refuses unqualified cell ranges inside a defined-name body (0x800A03EC). The code tries to auto-qualify using the parent path's first segment if it is an existing worksheet (and not 'namedrange'/'workbook'); only if that rescue fails does it throw. So this fires specifically when neither the ref nor the path supplies a sheet.","triggerScenarios":"Calling `add /namedrange --type namedrange --prop name=X --prop ref=A1:B1` (path is the generic `namedrange`, not a sheet, so no auto-qualify) or `add /workbook --type namedrange --prop ref=A1`. Supplying `ref=Sheet1!A1:B1` avoids it; supplying a sheet path like `/Sheet1` auto-qualifies the bare range.","commonSituations":"Adding a workbook-scope name with a bare cell range forgetting that defined names require an explicit sheet; assuming the active sheet is implied (it is not in OOXML defined names).","solutions":["Qualify the range with a sheet: `--prop ref=Sheet1!A1:B1`.","Or add via a sheet path so it auto-qualifies: `add ./book.xlsx /Sheet1 --type namedrange --prop name=X --prop ref=A1:B1`."],"exampleFix":"// before\nadd ./book.xlsx /namedrange --type namedrange --prop name=X --prop ref=A1:B1\n// after\nadd ./book.xlsx /namedrange --type namedrange --prop name=X --prop ref=Sheet1!A1:B1","handlingStrategy":"validation","validationCode":"// Ensure every bare A1 range in a defined name carries a sheet qualifier.\nvar bareRange = @\"^[A-Za-z]{1,3}\\d+(:[A-Za-z]{1,3}\\d+)?$\";\nif (!refVal.Contains('!') && Regex.IsMatch(refVal.Replace(\"$\",\"\"), bareRange)\n    && !CanAutoQualifyFromPath(parentPath))\n    throw new InvalidOperationException(\n        $\"Defined-name ref '{refVal}' needs a sheet qualifier: use SheetName!A1:B1\");","typeGuard":"static bool RefHasSheetQualifierOrIsNotBareRange(string refVal)\n    => refVal.Contains('!')\n    || !System.Text.RegularExpressions.Regex.IsMatch(\n        refVal.Replace(\"$\",\"\"), @\"^[A-Za-z]{1,3}\\d+(:[A-Za-z]{1,3}\\d+)?$\");","tryCatchPattern":"try { handler.AddNamedRange(...); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"no sheet qualifier\"))\n{ /* re-issue with SheetName! prefixed, or via a sheet path */ }","preventionTips":["Always qualify defined-name ranges with a sheet: Sheet1!A1:B1.","Or add the name via a sheet path (/Sheet1) so it auto-qualifies.","Never assume an 'active sheet' is implied for defined names — OOXML requires explicit qualification."],"tags":["excel","named-range","defined-name","sheet-qualifier","input-validation"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}