{"record":{"id":"ab835e93a6ada315","repo":"PHPOffice/PhpSpreadsheet","slug":"invalid-r1c1-format-cell-reference-value-out-of-r","errorCode":null,"errorMessage":"Invalid R1C1-format Cell Reference, Value out of range","messagePattern":"Invalid R1C1-format Cell Reference, Value out of range","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Cell/AddressHelper.php","lineNumber":67,"sourceCode":"            $rowReference = (string) $currentRowNumber;\n        }\n        //    Bracketed R references are relative to the current row\n        if ($rowReference[0] === '[') {\n            $rowReference = $currentRowNumber + (int) trim($rowReference, '[]');\n        }\n        $columnReference = $cellReference[4];\n        //    Empty C reference is the current column\n        if ($columnReference === '') {\n            $columnReference = (string) $currentColumnNumber;\n        }\n        //    Bracketed C references are relative to the current column\n        if ($columnReference[0] === '[') {\n            $columnReference = $currentColumnNumber + (int) trim($columnReference, '[]');\n        }\n        $columnReference = (int) $columnReference;\n\n        if ($columnReference <= 0 || $rowReference <= 0) {\n            throw new Exception('Invalid R1C1-format Cell Reference, Value out of range');\n        }\n        $A1CellReference = Coordinate::stringFromColumnIndex($columnReference) . $rowReference;\n\n        return $A1CellReference;\n    }\n\n    protected static function convertSpreadsheetMLFormula(string $formula): string\n    {\n        $formula = substr($formula, 3);\n        $temp = explode('\"', $formula);\n        $key = false;\n        foreach ($temp as &$value) {\n            //    Only replace in alternate array entries (i.e. non-quoted blocks)\n            $key = $key === false;\n            if ($key) {\n                $value = str_replace(['[.', ':.', ']'], ['', ':', ''], $value);\n            }\n        }","sourceCodeStart":49,"sourceCodeEnd":85,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Cell/AddressHelper.php#L49-L85","documentation":"The range check at the end of AddressHelper::convertToA1(): after resolving relative bracketed offsets against the anchor row/column, either result must be >= 1 (spreadsheets have no row/column 0). 'R[-5]C2' anchored at row 3 resolves to row -2 and throws PhpOffice\\PhpSpreadsheet\\Exception 'Invalid R1C1-format Cell Reference, Value out of range'. So the syntax was valid but the relative reference escapes the sheet.","triggerScenarios":"convertToA1('R[-5]C1', 3, 1) -> row -2; convertToA1('R1C[-3]', 1, 2) -> column -1; relative references read from formulas that were authored against a different anchor cell; defaults currentRowNumber/currentColumnNumber = 1 combined with any negative offset.","commonSituations":"Shifting a formula's anchor cell without recomputing its relative R1C1 offsets (cut/paste of formula text between cells); parsing R1C1 formulas from files where the stored anchor differs from the cell you process; batch transformations that re-anchor formulas near the top-left edge of the sheet.","solutions":["Pass the correct anchor: the row/column of the cell the formula actually lives in","Clamp or reject offsets that would leave the sheet before calling (compute anchor + offset >= 1)","When re-anchoring formulas, recompute relative offsets rather than reusing them","Wrap conversion in try/catch and skip/report formulas whose offsets are unresolvable at the new anchor"],"exampleFix":"// before: formula anchored at A3 carries R[-5]\n$a1 = AddressHelper::convertToA1('R[-5]C1', 3, 1); // throws\n\n// after: check the resolved target first\n$row = 3 + (-5);\nif ($row < 1) {\n    // offset invalid at this anchor; use absolute or adjust\n    $a1 = AddressHelper::convertToA1('R1C1', 3, 1);\n} else {\n    $a1 = AddressHelper::convertToA1('R[-5]C1', 3, 1);\n}","handlingStrategy":"validation","validationCode":"// Resolve offsets first and verify they stay on the sheet\npreg_match('/^R(?:\\[(-?\\d+)\\]|(\\d*))C(?:\\[(-?\\d+)\\]|(\\d*))$/i', $ref, $m);\n$row = $m[1] !== '' ? $currentRow + (int) $m[1] : (int) ($m[2] ?: $currentRow);\n$col = $m[3] !== '' ? $currentCol + (int) $m[3] : (int) ($m[4] ?: $currentCol);\nif ($row < 1 || $col < 1) {\n    throw new InvalidArgumentException('relative offset leaves the sheet');\n}","typeGuard":"function resolvesOnSheet(string $ref, int $r, int $c): bool\n{\n    if (!preg_match('/^R(?:\\[(-?\\d+)\\])?(?:C(?:\\[(-?\\d+)\\])?)?$/i', $ref, $m)) return false;\n    return ($r + (int) ($m[1] ?? 0)) >= 1 && ($c + (int) ($m[2] ?? 0)) >= 1;\n}","tryCatchPattern":"try {\n    $a1 = AddressHelper::convertToA1($ref, $row, $col);\n} catch (\\PhpOffice\\PhpSpreadsheet\\Exception $e) {\n    if (str_contains($e->getMessage(), 'out of range')) {\n        // clamp, make absolute, or skip this formula\n    } else { throw $e; }\n}","preventionTips":["Always pass the formula cell's real row/column as the anchor","When re-anchoring formulas, recompute R[..]/C[..] offsets","Negative offsets near row 1 / column A are the classic trigger - special-case them","Default anchors are 1,1: any negative offset with defaults will throw"],"tags":["phpspreadsheet","cell-reference","r1c1","relative-reference","out-of-range","address-conversion"],"backgroundTag":"invalid-cell-reference","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}