iOfficeAI/OfficeCLI · error · ArgumentException

Property 'formula2' is required when operator='{dv.Operator.

Error message

Property 'formula2' is required when operator='{dv.Operator.InnerText}'; supply both bounds (formula1=lower, formula2=upper).

What it means

Thrown by AddValidation when operator is between or notBetween but no formula2 property is supplied. Without the upper bound Excel silently treats the rule as 'anything passes' (file opens but validates nothing); the handler rejects it up front to avoid landing a permissive no-op on disk.

Source

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

        }

        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
            // permissive no-op on disk.
            throw new ArgumentException(
                $"Property 'formula2' is required when operator='{dv.Operator.InnerText}'; supply both bounds (formula1=lower, formula2=upper).");
        }

        // CONSISTENCY(tracking-rebind): previously we copied `properties`
        // into a fresh OrdinalIgnoreCase dictionary, but the copy constructor
        // walks IEnumerable<KVP> via the source's GetEnumerator, which on
        // TrackingPropertyDictionary marks EVERY input key as consumed —
        // including genuinely unknown ones like errorMessage=. That silently
        // hid unsupported_property warnings (R44 major-1). Read each known
        // key directly off `properties` (its custom comparer is already
        // OrdinalIgnoreCase and fires tracking only on actual TryGetValue
        // hits). Mirrors the AutoFilter pattern below.
        dv.AllowBlank = !properties.TryGetValue("allowBlank", out var dvAllowBlank)
            || IsTruthy(dvAllowBlank);
        dv.ShowErrorMessage = !properties.TryGetValue("showError", out var dvShowError)
            || IsTruthy(dvShowError);
        dv.ShowInputMessage = !properties.TryGetValue("showInput", out var dvShowInput)
            || IsTruthy(dvShowInput);

View on GitHub (pinned to 1ced45e900)

Solutions

1. Add properties["formula2"] as the upper bound (formula1 is lower, formula2 is upper).
2. Switch operator to a single-bound one (e.g. greaterThan) if only one bound is intended.
3. Pre-validate that between/notBetween always carries both formula1 and formula2.

Example fix

// before
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "whole", ["operator"] = "between", ["formula1"] = "1" });
// after
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "whole", ["operator"] = "between",
            ["formula1"] = "1", ["formula2"] = "100" });

var op = properties.GetValueOrDefault("operator");
bool needsBoth = op.Equals("between", StringComparison.OrdinalIgnoreCase)
              || op.Equals("notbetween", StringComparison.OrdinalIgnoreCase);
if (needsBoth && !properties.ContainsKey("formula2"))
    throw new InvalidOperationException($"operator '{op}' requires formula2");

static bool OperatorNeedsTwoBounds(string? op) =>
    op is not null && (op.Equals("between", StringComparison.OrdinalIgnoreCase)
                    || op.Equals("notbetween", StringComparison.OrdinalIgnoreCase));

When it happens

Trigger: Call Add type "validation" with operator "between" or "notBetween" and a properties dictionary that has no formula2 key.

Common situations: Setting operator=between and supplying only the lower bound; reusing a template that omits the upper bound; assuming formula2 defaults to formula1.

Related errors

• Property 'sqref' (or 'range'/'ref') is required for validati
• 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
• Unknown operator: {dvOp}

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