iOfficeAI/OfficeCLI · error · ArgumentException

validation formula1 is

Error message

validation formula1 is {dvFormula1.Length} chars; Excel's limit is 255. Put the list in a range and reference it instead.

What it means

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.

Solutions

1. Write the options into worksheet cells and reference the range in formula1 instead.
2. Shorten the literal list to <=255 characters.
3. If a long formula is genuinely needed, store its inputs in cells and reference them.

Example fix

// before (long inline list)
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "list", ["formula1"] = longListOf300Chars });
// after (options live in Z1:Z50, referenced by range)
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "list", ["formula1"] = "=$Z$1:$Z$50" });

if (properties.TryGetValue("formula1", out var f1) && f1.Length > 255)
    throw new InvalidOperationException($"formula1 is {f1.Length} chars; Excel cap is 255");

When it happens

Trigger: Call Add type "validation" with properties["formula1"] containing more than 255 characters (e.g. a very long inline literal option list).

Common situations: Inlining a large picklist; pasting a long computed formula; concatenating many options without realizing the length cap applies to the whole formula1 string.

Related errors

• validation formula2 is
• Defined name ref ' ' contains '#' outside a known error…
• Defined name ref ' ' has a dangling '!' — a sheet qualifier…
• Formula contains out-of-range cell reference
• Invalid array constant

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/10bf1570de9f7e5f. Report an issue: GitHub.

Appendix: source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs:574

                "greaterthanorequal" => DataValidationOperatorValues.GreaterThanOrEqual,
                "lessthanorequal" => DataValidationOperatorValues.LessThanOrEqual,
                _ => throw new ArgumentException($"Unknown operator: {dvOp}")
            };
        }

        if (properties.TryGetValue("formula1", out var dvFormula1))
        {
            // R28-A1 — reject empty formula1 for type=list. Excel renders an empty
            // dropdown (or rejects the file outright depending on form), and the
            // user almost certainly meant to provide options like "1,2,3".
            if (dv.Type?.Value == DataValidationValues.List
                && string.IsNullOrWhiteSpace(dvFormula1.Trim('"')))
                throw new ArgumentException(
                    "Property 'formula1' is empty for validation type=list; supply options like formula1=\"1,2,3\" or a range reference.");
            // Excel caps data-validation formulas at 255 chars; longer ones
            // pass schema validation but the file is refused (0x800A03EC).
            if (dvFormula1.Length > 255)
                throw new ArgumentException(
                    $"validation formula1 is {dvFormula1.Length} chars; Excel's limit is 255. Put the list in a range and reference it instead.");
            // Embedded double quotes inside a literal list (not a range ref)
            // produce list literals Excel refuses to open — empirically
            // verified; reject rather than write a corrupt file.
            if (dv.Type?.Value == DataValidationValues.List)
            {
                var inner = dvFormula1.Trim();
                if (inner.StartsWith('"') && inner.EndsWith('"') && inner.Length >= 2)
                    inner = inner[1..^1];
                if (inner.Contains('"'))
                    throw new ArgumentException(
                        "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.");
            }
            // Non-list formulas land in the A1-only <x:formula1> element —
            // an R1C1-style ref makes real Excel refuse the file (0x800A03EC)
            // while schema validation stays green. (For type=list the text is
            // a literal option list, not a formula, so it's left alone.)
            if (dv.Type?.Value != DataValidationValues.List)

View on GitHub (pinned to 1ced45e900)