PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Writer\Exception

Unknown sheet name $ext_ref in formula

Error message

Unknown sheet name $ext_ref in formula

What it means

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.

Source

Thrown at src/PhpSpreadsheet/Writer/Xls/Parser.php:835

            [$sheet_name1, $sheet_name2] = explode(':', $ext_ref);

            $sheet1 = $this->getSheetIndex($sheet_name1);
            if ($sheet1 == -1) {
                throw new WriterException("Unknown sheet name $sheet_name1 in formula");
            }
            $sheet2 = $this->getSheetIndex($sheet_name2);
            if ($sheet2 == -1) {
                throw new WriterException("Unknown sheet name $sheet_name2 in formula");
            }

            // Reverse max and min sheet numbers if necessary
            if ($sheet1 > $sheet2) {
                [$sheet1, $sheet2] = [$sheet2, $sheet1];
            }
        } else { // Single sheet name only.
            $sheet1 = $this->getSheetIndex($ext_ref);
            if ($sheet1 == -1) {
                throw new WriterException("Unknown sheet name $ext_ref in formula");
            }
            $sheet2 = $sheet1;
        }

        // assume all references belong to this document
        $supbook_index = 0x00;
        $ref = pack('vvv', $supbook_index, $sheet1, $sheet2);
        $totalreferences = count($this->references);
        $index = -1;
        for ($i = 0; $i < $totalreferences; ++$i) {
            if ($ref == $this->references[$i]) {
                $index = $i;

                break;
            }
        }
        // if REF was not found add it to references array
        if ($index == -1) {

View on GitHub (pinned to 65b080eef4)

Solutions

1. Ensure the referenced sheet exists with the exact name before save(): in_array($name, $spreadsheet->getSheetNames(), true).
2. Wrap sheet names containing spaces/punctuation in single quotes inside the formula: ='My Sheet'!A1.
3. Rebuild formulas after removing/renaming sheets instead of leaving dangling references.
4. Use the Xlsx writer if the workbook must carry references not resolvable at write time.

Example fix

// before
$sheet->getCell('B2')->setValue("='Q 2'!A1"); // actual tab name is 'Q2'
(new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet))->save('out.xls');
// Unknown sheet name Q 2 in formula

// after
$sheet->getCell('B2')->setValue("='Q2'!A1");
// or normalize names when setting:
// $formula = str_replace('Q 2', 'Q2', $formula);

$sheetNames = $spreadsheet->getSheetNames();
foreach ($sheet->getCoordinates() as $coord) {
    $v = $sheet->getCell($coord)->getValue();
    if (is_string($v) && preg_match_all("/(?:'([^']+)'!|([A-Za-z0-9_\.][A-Za-z0-9_\. ]*)!)/U", $v, $m)) {
        foreach (array_filter(array_merge($m[1], $m[2])) as $ref) {
            if (!in_array($ref, $sheetNames, true)) {
                throw new RuntimeException("Formula in {$coord} references unknown sheet '{$ref}'");
            }
        }
    }
}
(new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet))->save($path);

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• Unknown token $token
• Unknown range separator
• Cannot yet write formulae with defined names to Xls
• ')' token expected.
• Syntax error: $currentToken, lookahead: $lookAhead, current

AI-assisted analysis of PHPOffice/PhpSpreadsheet@65b080eef4 (2026-08-17). Data as JSON: /api/errors/0eced8e4b83fff4c. Report an issue: GitHub.