{"record":{"id":"35a1029f89bead26","repo":"iOfficeAI/OfficeCLI","slug":"property-formula1-is-required-for-validation-typ","errorCode":null,"errorMessage":"Property 'formula1' is required for validation type=list; supply options like formula1=\"1,2,3\" or a range reference.","messagePattern":"Property 'formula1' is required for validation type=list; supply options like formula1=\"1,2,3\" or a range reference\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs","lineNumber":599,"sourceCode":"                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)\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","sourceCodeStart":581,"sourceCodeEnd":617,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs#L581-L617","documentation":"Thrown by AddValidation in the else-if branch: type is list but the properties dictionary contains no formula1 key at all (distinct from 568, where formula1 is present but empty). A list validation with no source is nonsense, so it is rejected rather than persisted.","triggerScenarios":"Call Add type \"validation\" with properties[\"type\"]=\"list\" and no formula1 entry whatsoever.","commonSituations":"Forgetting formula1 when type=list; assuming the API defaults to a range; setting type first in a multi-step builder and never adding formula1.","solutions":["Add properties[\"formula1\"] with options (\"1,2,3\") or a range reference.","Switch type away from list if you did not intend a dropdown.","Pre-validate that type=list implies a non-empty formula1."],"exampleFix":"// before\nhandler.Add(\"/Sheet1\", \"validation\", null,\n    new() { [\"sqref\"] = \"A1:A5\", [\"type\"] = \"list\" });\n// after\nhandler.Add(\"/Sheet1\", \"validation\", null,\n    new() { [\"sqref\"] = \"A1:A5\", [\"type\"] = \"list\", [\"formula1\"] = \"1,2,3\" });","handlingStrategy":"validation","validationCode":"if (properties.GetValueOrDefault(\"type\").Equals(\"list\", StringComparison.OrdinalIgnoreCase)\n    && !properties.ContainsKey(\"formula1\"))\n    throw new InvalidOperationException(\"type=list requires a formula1 entry\");","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Treat type=list and formula1 as a required pair in your builder.","If no source is intended, do not choose type=list.","Unit-test the builder to ensure formula1 is always emitted for list."],"tags":["excel","validation","list","required-property"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}