{"record":{"id":"0eced8e4b83fff4c","repo":"PHPOffice/PhpSpreadsheet","slug":"unknown-sheet-name-ext-ref-in-formula","errorCode":null,"errorMessage":"Unknown sheet name $ext_ref in formula","messagePattern":"Unknown sheet name \\$ext_ref in formula","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Writer\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Writer/Xls/Parser.php","lineNumber":835,"sourceCode":"            [$sheet_name1, $sheet_name2] = explode(':', $ext_ref);\n\n            $sheet1 = $this->getSheetIndex($sheet_name1);\n            if ($sheet1 == -1) {\n                throw new WriterException(\"Unknown sheet name $sheet_name1 in formula\");\n            }\n            $sheet2 = $this->getSheetIndex($sheet_name2);\n            if ($sheet2 == -1) {\n                throw new WriterException(\"Unknown sheet name $sheet_name2 in formula\");\n            }\n\n            // Reverse max and min sheet numbers if necessary\n            if ($sheet1 > $sheet2) {\n                [$sheet1, $sheet2] = [$sheet2, $sheet1];\n            }\n        } else { // Single sheet name only.\n            $sheet1 = $this->getSheetIndex($ext_ref);\n            if ($sheet1 == -1) {\n                throw new WriterException(\"Unknown sheet name $ext_ref in formula\");\n            }\n            $sheet2 = $sheet1;\n        }\n\n        // assume all references belong to this document\n        $supbook_index = 0x00;\n        $ref = pack('vvv', $supbook_index, $sheet1, $sheet2);\n        $totalreferences = count($this->references);\n        $index = -1;\n        for ($i = 0; $i < $totalreferences; ++$i) {\n            if ($ref == $this->references[$i]) {\n                $index = $i;\n\n                break;\n            }\n        }\n        // if REF was not found add it to references array\n        if ($index == -1) {","sourceCodeStart":817,"sourceCodeEnd":853,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Writer/Xls/Parser.php#L817-L853","documentation":"Single-sheet variant of the sheet resolution failure: when a formula reference has no colon in the sheet part (e.g. ='My Sheet'!A1), getRefIndex() looks up that one name, strips surrounding quotes, unescapes doubled quotes, and throws if getSheetIndex() returns -1. The message embeds the exact (unquoted) name that failed to match.","triggerScenarios":"Any formula referencing a sheet absent from the workbook: =OtherSheet!A1+1 where OtherSheet does not exist; names differing by case/whitespace; quotes lost so a name with a space is looked up raw; the referenced sheet was removed by removeSheetByIndex() after the formula was written.","commonSituations":"Imported templates referencing helper sheets that the pipeline drops; users typing sheet names freely; case-sensitive comparisons surprising developers ('DATA' vs 'Data'); formulas generated from database values that contain trailing whitespace in the tab name.","solutions":["Ensure the referenced sheet exists with the exact name before save(): in_array($name, $spreadsheet->getSheetNames(), true).","Wrap sheet names containing spaces/punctuation in single quotes inside the formula: ='My Sheet'!A1.","Rebuild formulas after removing/renaming sheets instead of leaving dangling references.","Use the Xlsx writer if the workbook must carry references not resolvable at write time."],"exampleFix":"// before\n$sheet->getCell('B2')->setValue(\"='Q 2'!A1\"); // actual tab name is 'Q2'\n(new \\PhpOffice\\PhpSpreadsheet\\Writer\\Xls($spreadsheet))->save('out.xls');\n// Unknown sheet name Q 2 in formula\n\n// after\n$sheet->getCell('B2')->setValue(\"='Q2'!A1\");\n// or normalize names when setting:\n// $formula = str_replace('Q 2', 'Q2', $formula);","handlingStrategy":"validation","validationCode":"$sheetNames = $spreadsheet->getSheetNames();\nforeach ($sheet->getCoordinates() as $coord) {\n    $v = $sheet->getCell($coord)->getValue();\n    if (is_string($v) && preg_match_all(\"/(?:'([^']+)'!|([A-Za-z0-9_\\.][A-Za-z0-9_\\. ]*)!)/U\", $v, $m)) {\n        foreach (array_filter(array_merge($m[1], $m[2])) as $ref) {\n            if (!in_array($ref, $sheetNames, true)) {\n                throw new RuntimeException(\"Formula in {$coord} references unknown sheet '{$ref}'\");\n            }\n        }\n    }\n}\n(new \\PhpOffice\\PhpSpreadsheet\\Writer\\Xls($spreadsheet))->save($path);","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Run a sheet-reference audit on imported templates before converting to Xls.","Normalize sheet names (trim, single spaces) at creation; formulas and tab names then match byte-for-byte.","Quote names with spaces consistently - never sometimes-quoted, sometimes-not."],"tags":["xls","biff8","formula","sheet-name","cross-sheet"],"backgroundTag":"unknown-sheet-reference","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}