{"record":{"id":"318151375cb113d5","repo":"iOfficeAI/OfficeCLI","slug":"datavalidation-sqref-nr-overlaps-existing-vali","errorCode":null,"errorMessage":"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.","messagePattern":"DataValidation sqref '(.+?)' overlaps existing validation sqref '(.+?)'; Excel ignores stacked validations on the same cells\\. Remove the existing validation first or use a non-overlapping range\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs","lineNumber":685,"sourceCode":"        else if (properties.TryGetValue(\"showDropDown\", out var dvShowDd))\n            dv.ShowDropDown = ParseHelpers.IsTruthy(dvShowDd);\n\n        var wsEl = GetSheet(dvWorksheet);\n        var dvs = wsEl.GetFirstChild<DataValidations>();\n        // R27-3: stacking a second DV on a sqref that overlaps an existing\n        // DV is silently invisible in Excel (first wins). Reject up-front\n        // rather than persist a useless rule.\n        if (dvs != null)\n        {\n            var newRanges = dvSqref.Split(' ', StringSplitOptions.RemoveEmptyEntries);\n            foreach (var existing in dvs.Elements<DataValidation>())\n            {\n                var existingSqref = existing.SequenceOfReferences?.InnerText ?? \"\";\n                var existingRanges = existingSqref.Split(' ', StringSplitOptions.RemoveEmptyEntries);\n                foreach (var nr in newRanges)\n                    foreach (var er in existingRanges)\n                        if (RangesOverlap(nr, er))\n                            throw new ArgumentException(\n                                $\"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.\");\n            }\n        }\n        if (dvs == null)\n        {\n            dvs = new DataValidations();\n            var insertAfter = wsEl.GetFirstChild<Hyperlinks>() as OpenXmlElement\n                ?? wsEl.Elements<ConditionalFormatting>().LastOrDefault() as OpenXmlElement\n                ?? wsEl.GetFirstChild<SheetData>() as OpenXmlElement;\n            if (insertAfter is Hyperlinks)\n                insertAfter.InsertBeforeSelf(dvs);\n            else if (insertAfter != null)\n                insertAfter.InsertAfterSelf(dvs);\n            else\n                wsEl.AppendChild(dvs);\n        }\n\n        dvs.AppendChild(dv);","sourceCodeStart":667,"sourceCodeEnd":703,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs#L667-L703","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Remove or widen the existing DataValidation first so the ranges do not overlap.","Use a non-overlapping sqref for the new validation.","Consolidate overlapping rules into a single DataValidation covering the union."],"exampleFix":"// before (A1:A5 already validated, now adding A3:A10)\nhandler.Add(\"/Sheet1\", \"validation\", null,\n    new() { [\"sqref\"] = \"A3:A10\", [\"type\"] = \"whole\" });\n// after (use a non-overlapping range, or remove the old one first)\nhandler.Add(\"/Sheet1\", \"validation\", null,\n    new() { [\"sqref\"] = \"A6:A10\", [\"type\"] = \"whole\" });","handlingStrategy":"try-catch","validationCode":"// Pre-check: read existing validations on the sheet and reject overlap before Add.\nvar existing = handler.Query($\"/{sheet}/dataValidation\")\n    .Select(n => n.Properties.GetValueOrDefault(\"sqref\", \"\"));\nforeach (var tok in newSqref.Split(' ', StringSplitOptions.RemoveEmptyEntries))\n    foreach (var ex in existing.SelectMany(s => s.Split(' ', StringSplitOptions.RemoveEmptyEntries)))\n        if (RangesOverlapLocal(tok, ex)) throw new InvalidOperationException($\"overlap: {tok} vs {ex}\");","typeGuard":null,"tryCatchPattern":"try { handler.Add(\"/Sheet1\", \"validation\", null, props); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"overlaps existing validation sqref\"))\n{\n    // remove the existing validation on that range first, or pick a disjoint sqref\n}","preventionTips":["Plan validation ranges as a partition so they never share a cell.","Be careful with whole-column/whole-row ranges that silently intersect others.","Before adding, dump existing sqref tokens and subtract from your target."],"tags":["excel","validation","overlap","collision","range"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}