{"record":{"id":"addb1341c5a22823","repo":"iOfficeAI/OfficeCLI","slug":"cross-workbook-references-like-refval-require","errorCode":null,"errorMessage":"Cross-workbook references like '{refVal}' require an externalLinks part which officecli doesn't expose; use raw-set for this case","messagePattern":"Cross-workbook references like '(.+?)' require an externalLinks part which officecli doesn't expose; use raw-set for this case","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs","lineNumber":103,"sourceCode":"\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\n        // `Table1[@Col]` or `Table1[Price]` aren't falsely rejected.\n        var refValProbe = refVal.TrimStart(' ', '\\t').TrimStart('\\'');\n        if (System.Text.RegularExpressions.Regex.IsMatch(refValProbe,\n                @\"^\\[(\\d+|[^\\]]*\\.xls[xbm]?)\\]\",\n                System.Text.RegularExpressions.RegexOptions.IgnoreCase))\n            throw new ArgumentException(\n                $\"Cross-workbook references like '{refVal}' require an externalLinks part which officecli doesn't expose; use raw-set for this case\");\n\n        // Sheet-qualified refs must name an existing sheet with a plausible\n        // range — garbage like \"乱码!!!\" written verbatim made real Excel\n        // refuse the file while schema validation stayed green.\n        // 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)","sourceCodeStart":85,"sourceCodeEnd":121,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs#L85-L121","documentation":"Thrown when the resolved ref/refersTo/formula value starts with an external (cross-workbook) reference, detected by the regex `^\\[(\\d+|[^\\]]*\\.xls[xbm]?)\\]` after stripping leading whitespace/quote. Forms like `[Other.xlsx]Sheet1!$A$1` or `[1]Sheet1!A1` (and the single-quoted variant) require an externalLinks part that officecli does not expose. Writing such a ref without the part produces a silently broken defined name (formulas show #REF!), so it is refused. Structured refs like `Table1[Price]` are deliberately NOT matched.","triggerScenarios":"Passing `--prop ref=[Other.xlsx]Sheet1!A1`, `--prop ref=[1]Sheet1!A1`, or the quoted form `--prop ref='[Book.xlsx]Sheet'!A1`. Using `Table1[Price]` does NOT trigger this (it is a valid structured reference).","commonSituations":"Copying a defined name from a workbook that linked to another file; consolidating workbooks but keeping external links; paste from Excel's formula bar where external refs are common.","solutions":["Inline the external data into the current workbook first, then reference it locally (e.g. `ref=Sheet1!A1`).","If you truly need a cross-workbook link, use the raw-set escape hatch to author the externalLinks part yourself (as the message suggests).","Convert the external ref to a structured/table reference if the data is already local."],"exampleFix":"// before\nadd ./book.xlsx /namedrange --type namedrange --prop name=X --prop ref=[Other.xlsx]Sheet1!A1\n// after\nadd ./book.xlsx /namedrange --type namedrange --prop name=X --prop ref=Sheet1!A1","handlingStrategy":"validation","validationCode":"// Detect external/cross-workbook refs the same way the handler does.\nvar probe = refVal.TrimStart(' ','\\t').TrimStart('\\'');\nif (Regex.IsMatch(probe, @\"^\\[(\\d+|[^\\]]*\\.xls[xbm]?)\\]\", RegexOptions.IgnoreCase))\n    throw new InvalidOperationException(\n        $\"Cross-workbook ref '{refVal}' needs an externalLinks part; inline the data or use raw-set\");","typeGuard":"static bool IsCrossWorkbookRef(string? s)\n{\n    if (string.IsNullOrEmpty(s)) return false;\n    var probe = s.TrimStart(' ','\\t').TrimStart('\\'');\n    return System.Text.RegularExpressions.Regex.IsMatch(\n        probe, @\"^\\[(\\d+|[^\\]]*\\.xls[xbm]?)\\]\", RegexOptions.IgnoreCase);\n}","tryCatchPattern":"try { handler.AddNamedRange(...); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"Cross-workbook\"))\n{ /* route the user to raw-set or inline the data */ }","preventionTips":["Inline external data into the target workbook before defining a name over it.","Recognize structured refs (Table1[Col]) are NOT cross-workbook and are safe.","Audit pasted formulas for [Book.xlsx] or [1] prefixes before using them as a ref."],"tags":["excel","named-range","defined-name","cross-workbook","external-link","input-validation"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}