{"record":{"id":"9b1462ac405d1fb9","repo":"iOfficeAI/OfficeCLI","slug":"validation-formula-value-is-not-valid-for-this","errorCode":null,"errorMessage":"validation formula '{value}' is not valid for this validation type: expected a number, date, cell reference, or formula (a bare value with spaces is not valid formula syntax).","messagePattern":"validation formula '(.+?)' is not valid for this validation type: expected a number, date, cell reference, or formula \\(a bare value with spaces is not valid formula syntax\\)\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs","lineNumber":440,"sourceCode":"                var epoch = new System.DateTime(1899, 12, 30);\n                return ((int)(dt - epoch).TotalDays).ToString(System.Globalization.CultureInfo.InvariantCulture);\n            }\n        }\n        if (type == DataValidationValues.Custom)\n        {\n            if (value.StartsWith(\"=\"))\n                return value.Substring(1);\n        }\n        // For non-list numeric/date/text types, formula1/formula2 must be a\n        // number, date/time (handled above), cell/range ref, or a formula — a\n        // bare value containing whitespace (e.g. \"hello world\") is invalid\n        // OOXML formula syntax and makes real Excel refuse the file\n        // (0x800A03EC). Reject it up front. (Quoted literals and refs pass.)\n        if (type != DataValidationValues.Custom\n            && value.Any(char.IsWhiteSpace)\n            && !value.StartsWith(\"\\\"\") && !value.StartsWith(\"=\")\n            && !value.Contains('!') && !value.Contains('('))\n            throw new ArgumentException(\n                $\"validation formula '{value}' is not valid for this validation type: \" +\n                \"expected a number, date, cell reference, or formula (a bare value with spaces is not valid formula syntax).\");\n        return value;\n    }\n\n    // CONSISTENCY(merge-overlap): centralize the \"insert one MergeCell\"\n    // policy. Excel rejects overlapping <mergeCell> entries with a\n    // \"found a problem\" repair dialog, but the OOXML SDK happily\n    // appends them. Mirrors the T4 overlap-throws pattern used by\n    // tables and AutoFilter+table.\n    // - Exact-match ref: no-op (idempotent re-Add stays consistent\n    //   with prior dedup behavior).\n    // - Geometric overlap with a non-identical range: throw.\n    // - Otherwise: append.\n    private static readonly System.Text.RegularExpressions.Regex SingleMergeRefPattern =\n        new(@\"^[A-Z]+[0-9]+(:[A-Z]+[0-9]+)?$\",\n            System.Text.RegularExpressions.RegexOptions.Compiled);\n","sourceCodeStart":422,"sourceCodeEnd":458,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs#L422-L458","documentation":"For data-validation types other than 'list' and 'custom', formula1/formula2 must be a number, date/time, cell/range reference, or formula. A bare value containing whitespace (e.g. 'hello world') is not valid OOXML formula syntax and makes real Excel refuse the file with 0x800A03EC. The library rejects such values so the saved workbook stays openable.","triggerScenarios":"Calling a data-validation setter on a non-custom, non-list type with a value that: contains whitespace AND does not start with '\"' or '=' AND does not contain '!' or '('. All four conditions must hold to trip the guard.","commonSituations":"Passing a list of allowed values ('red, blue') to a numeric/text validation type instead of type=list; forgetting to prefix a formula with '='; quoting mistakes on string literals.","solutions":["Switch the validation type to 'list' and let the list normalizer quote the values.","Prefix the value with '=' so it is treated as a formula body.","Remove the whitespace so the value is a single number/date/ref token.","Quote the literal with double-quotes if it is a string."],"exampleFix":"// before\nsheet.AddDataValidation(\"A1:A10\", type: DataValidationValues.Text, formula1: \"hello world\");\n\n// after (option A: list type)\nsheet.AddDataValidation(\"A1:A10\", type: DataValidationValues.List, formula1: \"hello world\");\n// after (option B: formula)\nsheet.AddDataValidation(\"A1:A10\", type: DataValidationValues.Text, formula1: \"=LEN(A1)>5\");","handlingStrategy":"validation","validationCode":"static string NormalizeNonListValidationFormula(string value, DataValidationValues type) {\n    if (type == DataValidationValues.Custom || type == DataValidationValues.List) return value;\n    if (!value.Any(char.IsWhiteSpace)) return value;\n    if (value.StartsWith(\"\\\"\") || value.StartsWith(\"=\") || value.Contains('!') || value.Contains('(')) return value;\n    return \"=\" + value;   // promote to a formula body\n}","typeGuard":null,"tryCatchPattern":"try { sheet.AddDataValidation(range, type, formula1: v); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"not valid for this validation type\")) {\n    sheet.AddDataValidation(range, DataValidationValues.List, formula1: v);\n}","preventionTips":["Match the validation type to the data: multi-value text -> List, expression -> Custom.","Prefix formula bodies with '='; quote string literals with '\"'."],"tags":["excel","data-validation","formula","ooxml"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}