iOfficeAI/OfficeCLI · error · ArgumentException

Property 'formula1' is required for validation type=list; su

Error message

Property 'formula1' is required for validation type=list; supply options like formula1="1,2,3" or a range reference.

What it means

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.

Source

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

                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)
                ValidateNoR1C1Reference(dvFormula1);
            dv.Formula1 = new Formula1(NormalizeValidationFormula(dvFormula1, dv.Type?.Value));
        }
        else if (dv.Type?.Value == DataValidationValues.List)
        {
            // R28-A1 — type=list with no formula1 at all is also nonsense.
            throw new ArgumentException(
                "Property 'formula1' is required for validation type=list; supply options like formula1=\"1,2,3\" or a range reference.");
        }

        if (properties.TryGetValue("formula2", out var dvFormula2))
        {
            if (dvFormula2.Length > 255)
                throw new ArgumentException(
                    $"validation formula2 is {dvFormula2.Length} chars; Excel's limit is 255.");
            if (dv.Type?.Value != DataValidationValues.List)
                ValidateNoR1C1Reference(dvFormula2);
            dv.Formula2 = new Formula2(NormalizeValidationFormula(dvFormula2, dv.Type?.Value));
        }
        else if (dv.Operator?.Value == DataValidationOperatorValues.Between
                 || dv.Operator?.Value == DataValidationOperatorValues.NotBetween)
        {
            // operator=between/notBetween needs both bounds. Without formula2
            // Excel silently treats the rule as "anything passes" — the file
            // opens but validates nothing. Reject up front rather than land a

View on GitHub (pinned to 1ced45e900)

Solutions

1. Add properties["formula1"] with options ("1,2,3") or a range reference.
2. Switch type away from list if you did not intend a dropdown.
3. Pre-validate that type=list implies a non-empty formula1.

Example fix

// before
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "list" });
// after
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "list", ["formula1"] = "1,2,3" });

if (properties.GetValueOrDefault("type").Equals("list", StringComparison.OrdinalIgnoreCase)
    && !properties.ContainsKey("formula1"))
    throw new InvalidOperationException("type=list requires a formula1 entry");

When it happens

Trigger: Call Add type "validation" with properties["type"]="list" and no formula1 entry whatsoever.

Common situations: Forgetting formula1 when type=list; assuming the API defaults to a range; setting type first in a multi-step builder and never adding formula1.

Related errors

• Property 'sqref' (or 'range'/'ref') is required for validati
• Property 'formula1' is empty for validation type=list; suppl
• Chart requires a 'data' property. Use: data="Series1:1,2,3;S
• 'src' property is required for picture type
• Property 'ref' is required for comment

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