{"record":{"id":"cd2b383a9f6d635b","repo":"iOfficeAI/OfficeCLI","slug":"cross-workbook-references-like-formula-require","errorCode":null,"errorMessage":"Cross-workbook references like '{formula}' 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.Helpers.Validation.cs","lineNumber":364,"sourceCode":"    /// </summary>\n    internal static void RejectCrossWorkbookFormula(string formula)\n    {\n        if (string.IsNullOrEmpty(formula)) return;\n        var trimmed = formula.TrimStart('=', ' ', '\\t');\n        // CONSISTENCY(cross-workbook-vs-structured-ref): the older `^\\[` guard\n        // also matched OOXML structured table references like `[@Price]` and\n        // `[Price]*[Qty]`, falsely rejecting valid Excel-365 formulas. Real\n        // cross-workbook refs have one of two shapes:\n        //   - numeric workbook index:  `[1]Sheet1!A1`        → `[<digits>]`\n        //   - filename + extension:    `[Other.xlsx]Sheet!A1` → `[<name>.xls(x|m|b)?]`\n        // Both forms are followed by a sheet reference (`Sheet!...`), but the\n        // bracket payload alone is enough to disambiguate from `[@Col]` /\n        // `[Col]` structured refs (which contain `@`, alphabetics without an\n        // extension, or `:`).\n        if (System.Text.RegularExpressions.Regex.IsMatch(trimmed,\n                @\"^\\[(\\d+|[^\\]]*\\.xls[xbm]?)\\]\",\n                System.Text.RegularExpressions.RegexOptions.IgnoreCase))\n            throw new ArgumentException(\n                $\"Cross-workbook references like '{formula}' require an externalLinks part which officecli doesn't expose; use raw-set for this case\");\n    }\n\n    // Normalize user-supplied data-validation formula values so Excel accepts\n    // them. `type=list` auto-quotes bare lists. `type=time` accepts HH:MM /\n    // HH:MM:SS and converts to the Excel time serial fraction. `type=date`\n    // accepts YYYY-MM-DD and converts to the Excel date serial. `type=custom`\n    // strips a leading '=' since OOXML `<x:formula1>` expects the formula body\n    // without one.\n    internal static string NormalizeValidationFormula(string value, DataValidationValues? type)\n    {\n        if (string.IsNullOrEmpty(value)) return value;\n        if (type == DataValidationValues.List)\n        {\n            // list: wrap bare \"a,b,c\" in quotes; leave cell/range refs and\n            // already-quoted literals alone. V1: a leading `=` signals a\n            // formula-ref (e.g. `=VOpts`, `=$Z$1:$Z$5`) — strip the `=`\n            // (OOXML `<x:formula1>` expects the body without one) and","sourceCodeStart":346,"sourceCodeEnd":382,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs#L346-L382","documentation":"A formula begins with a cross-workbook reference — either a numeric workbook index like [1]Sheet1!A1 or a filename+extension like [Other.xlsx]Sheet1!A1. Such references need an externalLinks part to resolve, which officecli does not expose. Without it, Excel opens the file but the cell shows #REF!, so the library rejects up-front rather than persisting a broken formula.","triggerScenarios":"Setting a formula whose body starts with =[<digits>] or =[<name>.xls(x|m|b)?] per the IsMatch regex. The bracket payload alone disambiguates from structured refs like [@Col] or [Col]*[Qty].","commonSituations":"Copying formulas out of a workbook that had live external links; expecting the tool to wire up externalLinks automatically; migrating from a multi-file model.","solutions":["Use the raw-set escape hatch if cross-workbook resolution is genuinely required (the message names it).","Inline the referenced data into the current workbook and re-point the formula at the local range.","Replace the external ref with a value paste if live linking is not needed."],"exampleFix":"// before\nsheet.SetFormula(\"A1\", \"=[Other.xlsx]Sheet1!B2\");\n\n// after (option A: local ref)\nsheet.SetFormula(\"A1\", \"=Sheet1!B2\");\n// after (option B: raw-set if you manage externalLinks yourself)\nsheet.RawSetFormula(\"A1\", \"=[Other.xlsx]Sheet1!B2\");","handlingStrategy":"validation","validationCode":"static readonly Regex CrossWb =\n    new(@\"^\\[(\\d+|[^\\]]*\\.xls[xbm]?)\\]\", RegexOptions.IgnoreCase);\nstatic bool IsCrossWorkbook(string formula) =>\n    CrossWb.IsMatch((formula ?? string.Empty).TrimStart('=').Trim());","typeGuard":null,"tryCatchPattern":"try { sheet.SetFormula(cell, formula); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"Cross-workbook references\")) {\n    sheet.RawSetFormula(cell, formula);   // only if you manage externalLinks yourself\n}","preventionTips":["Inline external data into the workbook before writing formulas that reference it.","Scan pasted formulas for leading '[<digits>]' or '[name.xlsx]' tokens before set."],"tags":["excel","formula","cross-workbook","external-links"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}