{"record":{"id":"10bf1570de9f7e5f","repo":"iOfficeAI/OfficeCLI","slug":"validation-formula1-is-dvformula1-length-chars","errorCode":null,"errorMessage":"validation formula1 is {dvFormula1.Length} chars; Excel's limit is 255. Put the list in a range and reference it instead.","messagePattern":"validation formula1 is (.+?) chars; Excel's limit is 255\\. Put the list in a range and reference it instead\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs","lineNumber":574,"sourceCode":"                \"greaterthanorequal\" => DataValidationOperatorValues.GreaterThanOrEqual,\n                \"lessthanorequal\" => DataValidationOperatorValues.LessThanOrEqual,\n                _ => throw new ArgumentException($\"Unknown operator: {dvOp}\")\n            };\n        }\n\n        if (properties.TryGetValue(\"formula1\", out var dvFormula1))\n        {\n            // R28-A1 — reject empty formula1 for type=list. Excel renders an empty\n            // dropdown (or rejects the file outright depending on form), and the\n            // user almost certainly meant to provide options like \"1,2,3\".\n            if (dv.Type?.Value == DataValidationValues.List\n                && string.IsNullOrWhiteSpace(dvFormula1.Trim('\"')))\n                throw new ArgumentException(\n                    \"Property 'formula1' is empty for validation type=list; supply options like formula1=\\\"1,2,3\\\" or a range reference.\");\n            // Excel caps data-validation formulas at 255 chars; longer ones\n            // pass schema validation but the file is refused (0x800A03EC).\n            if (dvFormula1.Length > 255)\n                throw new ArgumentException(\n                    $\"validation formula1 is {dvFormula1.Length} chars; Excel's limit is 255. Put the list in a range and reference it instead.\");\n            // Embedded double quotes inside a literal list (not a range ref)\n            // produce list literals Excel refuses to open — empirically\n            // verified; reject rather than write a corrupt file.\n            if (dv.Type?.Value == DataValidationValues.List)\n            {\n                var inner = dvFormula1.Trim();\n                if (inner.StartsWith('\"') && inner.EndsWith('\"') && inner.Length >= 2)\n                    inner = inner[1..^1];\n                if (inner.Contains('\"'))\n                    throw new ArgumentException(\n                        \"validation list options must not contain double quotes; Excel refuses files with quoted-literal escapes inside a list formula. Put the options in cells and reference the range instead.\");\n            }\n            // 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)","sourceCodeStart":556,"sourceCodeEnd":592,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs#L556-L592","documentation":"Thrown by AddValidation when a formula1 value longer than 255 characters is supplied. Excel caps data-validation formulas at 255 chars; longer text passes schema validation but the file is refused with 0x800A03EC, so the handler rejects it instead of writing an unopenable workbook.","triggerScenarios":"Call Add type \"validation\" with properties[\"formula1\"] containing more than 255 characters (e.g. a very long inline literal option list).","commonSituations":"Inlining a large picklist; pasting a long computed formula; concatenating many options without realizing the length cap applies to the whole formula1 string.","solutions":["Write the options into worksheet cells and reference the range in formula1 instead.","Shorten the literal list to <=255 characters.","If a long formula is genuinely needed, store its inputs in cells and reference them."],"exampleFix":"// before (long inline list)\nhandler.Add(\"/Sheet1\", \"validation\", null,\n    new() { [\"sqref\"] = \"A1:A5\", [\"type\"] = \"list\", [\"formula1\"] = longListOf300Chars });\n// after (options live in Z1:Z50, referenced by range)\nhandler.Add(\"/Sheet1\", \"validation\", null,\n    new() { [\"sqref\"] = \"A1:A5\", [\"type\"] = \"list\", [\"formula1\"] = \"=$Z$1:$Z$50\" });","handlingStrategy":"validation","validationCode":"if (properties.TryGetValue(\"formula1\", out var f1) && f1.Length > 255)\n    throw new InvalidOperationException($\"formula1 is {f1.Length} chars; Excel cap is 255\");","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Put large picklists in cells and reference the range.","Check the length of inline formulas before calling.","Watch generated/concatenated formulas that can silently exceed 255."],"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"}