{"record":{"id":"423303135f5930fa","repo":"iOfficeAI/OfficeCLI","slug":"validation-formula2-is-dvformula2-length-chars","errorCode":null,"errorMessage":"validation formula2 is {dvFormula2.Length} chars; Excel's limit is 255.","messagePattern":"validation formula2 is (.+?) chars; Excel's limit is 255\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs","lineNumber":606,"sourceCode":"            // Non-list formulas land in the A1-only <x:formula1> element —\n            // an R1C1-style ref makes real Excel refuse the file (0x800A03EC)\n            // while schema validation stays green. (For type=list the text is\n            // a literal option list, not a formula, so it's left alone.)\n            if (dv.Type?.Value != DataValidationValues.List)\n                ValidateNoR1C1Reference(dvFormula1);\n            dv.Formula1 = new Formula1(NormalizeValidationFormula(dvFormula1, dv.Type?.Value));\n        }\n        else if (dv.Type?.Value == DataValidationValues.List)\n        {\n            // R28-A1 — type=list with no formula1 at all is also nonsense.\n            throw new ArgumentException(\n                \"Property 'formula1' is required for validation type=list; supply options like formula1=\\\"1,2,3\\\" or a range reference.\");\n        }\n\n        if (properties.TryGetValue(\"formula2\", out var dvFormula2))\n        {\n            if (dvFormula2.Length > 255)\n                throw new ArgumentException(\n                    $\"validation formula2 is {dvFormula2.Length} chars; Excel's limit is 255.\");\n            if (dv.Type?.Value != DataValidationValues.List)\n                ValidateNoR1C1Reference(dvFormula2);\n            dv.Formula2 = new Formula2(NormalizeValidationFormula(dvFormula2, dv.Type?.Value));\n        }\n        else if (dv.Operator?.Value == DataValidationOperatorValues.Between\n                 || dv.Operator?.Value == DataValidationOperatorValues.NotBetween)\n        {\n            // operator=between/notBetween needs both bounds. Without formula2\n            // Excel silently treats the rule as \"anything passes\" — the file\n            // opens but validates nothing. Reject up front rather than land a\n            // permissive no-op on disk.\n            throw new ArgumentException(\n                $\"Property 'formula2' is required when operator='{dv.Operator.InnerText}'; supply both bounds (formula1=lower, formula2=upper).\");\n        }\n\n        // CONSISTENCY(tracking-rebind): previously we copied `properties`\n        // into a fresh OrdinalIgnoreCase dictionary, but the copy constructor","sourceCodeStart":588,"sourceCodeEnd":624,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs#L588-L624","documentation":"Thrown by AddValidation when an optional formula2 property is supplied and is longer than 255 characters. The same 255-char Excel cap that applies to formula1 applies to formula2; the handler rejects the over-length upper bound instead of writing a file Excel will refuse.","triggerScenarios":"Call Add type \"validation\" with properties[\"formula2\"] longer than 255 characters (typically a long inline value or computed expression for the upper bound).","commonSituations":"A between/notBetween rule with a very large literal bound; pasting a long formula as the upper bound; reusing a long shared expression for both bounds.","solutions":["Move the upper-bound logic into a cell and reference it in formula2.","Shorten formula2 to <=255 characters.","Where possible, share one cell-backed expression for both bounds."],"exampleFix":"// before\nhandler.Add(\"/Sheet1\", \"validation\", null,\n    new() { [\"sqref\"] = \"A1:A5\", [\"type\"] = \"decimal\", [\"operator\"] = \"between\",\n            [\"formula1\"] = \"0\", [\"formula2\"] = longExprOf300Chars });\n// after (upper bound computed in Z1, referenced)\nhandler.Add(\"/Sheet1\", \"validation\", null,\n    new() { [\"sqref\"] = \"A1:A5\", [\"type\"] = \"decimal\", [\"operator\"] = \"between\",\n            [\"formula1\"] = \"0\", [\"formula2\"] = \"=$Z$1\" });","handlingStrategy":"validation","validationCode":"if (properties.TryGetValue(\"formula2\", out var f2) && f2.Length > 255)\n    throw new InvalidOperationException($\"formula2 is {f2.Length} chars; Excel cap is 255\");","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Move long upper-bound logic into a cell and reference it.","Apply the same 255-char budget to formula2 as to formula1.","Reuse a single cell-backed expression for shared bounds."],"tags":["excel","validation","size-limit","formula"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}