iOfficeAI/OfficeCLI · error · ArgumentException

DataValidation sqref '{nr}' overlaps existing validation sqr

Error message

DataValidation sqref '{nr}' overlaps existing validation sqref '{er}'; Excel ignores stacked validations on the same cells. Remove the existing validation first or use a non-overlapping range.

What it means

Thrown by AddValidation before the new DataValidation is attached. It splits the new sqref and every existing DataValidation's SequenceOfReferences into space-separated tokens and uses RangesOverlap on each pair; any shared cell rejects the add because Excel applies only the first validation on a cell and silently ignores the rest.

Source

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

        else if (properties.TryGetValue("showDropDown", out var dvShowDd))
            dv.ShowDropDown = ParseHelpers.IsTruthy(dvShowDd);

        var wsEl = GetSheet(dvWorksheet);
        var dvs = wsEl.GetFirstChild<DataValidations>();
        // R27-3: stacking a second DV on a sqref that overlaps an existing
        // DV is silently invisible in Excel (first wins). Reject up-front
        // rather than persist a useless rule.
        if (dvs != null)
        {
            var newRanges = dvSqref.Split(' ', StringSplitOptions.RemoveEmptyEntries);
            foreach (var existing in dvs.Elements<DataValidation>())
            {
                var existingSqref = existing.SequenceOfReferences?.InnerText ?? "";
                var existingRanges = existingSqref.Split(' ', StringSplitOptions.RemoveEmptyEntries);
                foreach (var nr in newRanges)
                    foreach (var er in existingRanges)
                        if (RangesOverlap(nr, er))
                            throw new ArgumentException(
                                $"DataValidation sqref '{nr}' overlaps existing validation sqref '{er}'; Excel ignores stacked validations on the same cells. Remove the existing validation first or use a non-overlapping range.");
            }
        }
        if (dvs == null)
        {
            dvs = new DataValidations();
            var insertAfter = wsEl.GetFirstChild<Hyperlinks>() as OpenXmlElement
                ?? wsEl.Elements<ConditionalFormatting>().LastOrDefault() as OpenXmlElement
                ?? wsEl.GetFirstChild<SheetData>() as OpenXmlElement;
            if (insertAfter is Hyperlinks)
                insertAfter.InsertBeforeSelf(dvs);
            else if (insertAfter != null)
                insertAfter.InsertAfterSelf(dvs);
            else
                wsEl.AppendChild(dvs);
        }

        dvs.AppendChild(dv);

View on GitHub (pinned to 1ced45e900)

Solutions

1. Remove or widen the existing DataValidation first so the ranges do not overlap.
2. Use a non-overlapping sqref for the new validation.
3. Consolidate overlapping rules into a single DataValidation covering the union.

Example fix

// before (A1:A5 already validated, now adding A3:A10)
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A3:A10", ["type"] = "whole" });
// after (use a non-overlapping range, or remove the old one first)
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A6:A10", ["type"] = "whole" });

// Pre-check: read existing validations on the sheet and reject overlap before Add.
var existing = handler.Query($"/{sheet}/dataValidation")
    .Select(n => n.Properties.GetValueOrDefault("sqref", ""));
foreach (var tok in newSqref.Split(' ', StringSplitOptions.RemoveEmptyEntries))
    foreach (var ex in existing.SelectMany(s => s.Split(' ', StringSplitOptions.RemoveEmptyEntries)))
        if (RangesOverlapLocal(tok, ex)) throw new InvalidOperationException($"overlap: {tok} vs {ex}");

try { handler.Add("/Sheet1", "validation", null, props); }
catch (ArgumentException ex) when (ex.Message.Contains("overlaps existing validation sqref"))
{
    // remove the existing validation on that range first, or pick a disjoint sqref
}

When it happens

Trigger: Call Add type "validation" whose sqref (any of its space-separated regions) geometrically overlaps an existing DataValidation's sqref on the same sheet, including whole-row/whole-column ranges which are expanded before the rectangle intersection test.

Common situations: Layering a stricter rule over a previously added range; adding validation to A1:A5 then again to A3:A10; whole-column validations (A:A) that intersect a later smaller range.

Related errors

• comment already exists on {cmtRefUpper}. Remove it first bef
• Invalid range '{spec}': offsets must be non-negative.
• Property 'sqref' (or 'range'/'ref') is required for validati
• AutoFilter range '{afRangeUpper}' overlaps existing table '{
• Invalid range: {range}

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