{"record":{"id":"4f47f209e34d62f8","repo":"iOfficeAI/OfficeCLI","slug":"formula-contains-out-of-range-cell-reference-m-v","errorCode":null,"errorMessage":"Formula contains out-of-range cell reference '{m.Value}'. Excel limits: rows 1-1048576, columns A-XFD.","messagePattern":"Formula contains out-of-range cell reference '(.+?)'\\. Excel limits: rows 1-1048576, columns A-XFD\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs","lineNumber":312,"sourceCode":"        // Match A1-style refs: optional $ + 1-3 letters + optional $ + 1-8 digits.\n        // (Excel's row ceiling 1048576 is 7-digit, but 8-digit numbers like\n        // A10000000 must still be caught so they're rejected with the clean\n        // \"out-of-range\" error rather than slipping through validation.)\n        // Avoid matching inside an identifier (e.g. \"FOO1\") via a leading\n        // boundary that requires either start-of-string or a non-letter.\n        var rx = new System.Text.RegularExpressions.Regex(\n            @\"(?<![A-Za-z_])\\$?([A-Za-z]{1,3})\\$?([0-9]{1,8})\\b\");\n        foreach (System.Text.RegularExpressions.Match m in rx.Matches(stripped))\n        {\n            var col = m.Groups[1].Value.ToUpperInvariant();\n            if (!long.TryParse(m.Groups[2].Value, out var row)) continue;\n            // Column index check: ColumnNameToIndex would throw on overflow,\n            // but we want a clean validation message. Compute manually.\n            int colIdx = 0;\n            foreach (var ch in col) colIdx = colIdx * 26 + (ch - 'A' + 1);\n            if (colIdx < 1 || colIdx > 16384 || row < 1 || row > 1048576)\n            {\n                throw new ArgumentException(\n                    $\"Formula contains out-of-range cell reference '{m.Value}'. \" +\n                    \"Excel limits: rows 1-1048576, columns A-XFD.\");\n            }\n        }\n    }\n\n    internal static void ValidateSheetName(string name)\n    {\n        if (string.IsNullOrWhiteSpace(name))\n            throw new ArgumentException(\"Invalid sheet name: name cannot be empty or whitespace.\");\n        if (name.Length > 31)\n            throw new ArgumentException(\n                $\"Invalid sheet name '{name}': length {name.Length} exceeds Excel's 31-char limit.\");\n        var forbidden = new[] { '\\\\', '/', '?', '*', ':', '[', ']' };\n        var hit = name.IndexOfAny(forbidden);\n        if (hit >= 0)\n            throw new ArgumentException(\n                $\"Invalid sheet name '{name}': contains forbidden character '{name[hit]}'. Excel rejects any of: \\\\ / ? * : [ ]\");","sourceCodeStart":294,"sourceCodeEnd":330,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs#L294-L330","documentation":"A formula passed to a set/validate operation contains a cell reference whose column exceeds Excel's 16384-column grid (A..XFD) or whose row exceeds 1048576. The library regex-extracts every A1 token from the formula and bounds-checks it so the saved OOXML cannot carry a reference Excel would misinterpret or refuse. This is a write-time guard, not an Excel runtime error.","triggerScenarios":"Calling a formula-setting or formula-validation API with a string containing a token like 'A1048577', 'XFE1', 'ZZZ1' (column 18278), or 'A99999999'. The regex `\\$?([A-Za-z]{1,3})\\$?([0-9]{1,8})` matches the token and the manual colIdx/row bounds check fails.","commonSituations":"Generated references from unbounded loops; copy-paste from Google Sheets (18,278 columns); a counter that overflowed or was never clamped; an extra digit typo like 'A10485770'.","solutions":["Inspect the formula string: the offending token is quoted verbatim in the message (the {m.Value} placeholder).","Clamp the generated row/col indices to [1,1048576] and [1,16384] before building the A1 string.","Use the library's own ColumnNameToIndex/IndexToColumnName helpers instead of hand-rolling column arithmetic, so overflow is impossible.","If you genuinely need a larger grid, that is not representable in OOXML — redesign to address ranges, not a single mega-cell."],"exampleFix":"// before\nsheet.SetFormula(\"A1\", \"=XFE1+1\");   // column 16385, out of range\n\n// after\nsheet.SetFormula(\"A1\", \"=XFD1+1\");   // column 16384, last legal column","handlingStrategy":"validation","validationCode":"static readonly Regex A1Token = new(@\"\\$?([A-Za-z]{1,3})\\$?([0-9]{1,8})\");\nstatic void AssertFormulaRefsInBounds(string formula) {\n    foreach (Match m in A1Token.Matches(formula)) {\n        int col = 0;\n        foreach (var ch in m.Groups[1].Value.ToUpperInvariant()) col = col * 26 + (ch - 'A' + 1);\n        long row = long.Parse(m.Groups[2].Value);\n        if (col < 1 || col > 16384 || row < 1 || row > 1048576)\n            throw new ArgumentOutOfRangeException(nameof(formula), $\"Out-of-range ref: {m.Value}\");\n    }\n}","typeGuard":null,"tryCatchPattern":"try { sheet.SetFormula(cell, formula); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"out-of-range cell reference\")) {\n    // log the offending token from the message and surface to caller\n}","preventionTips":["Always build A1 strings from clamped numeric indices via ColumnNameToIndex/IndexToColumnName.","When importing from non-Excel sources, map and clamp their grid to Excel's 16384x1048576 bounds."],"tags":["excel","formula","cell-reference","validation","ooxml"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}