{"record":{"id":"dab5fa9091402d3c","repo":"PHPOffice/PhpSpreadsheet","slug":"defined-named-definedname-is-a-formula-not-a-r","errorCode":null,"errorMessage":"Defined Named {$definedName} is a formula, not a range or cell.","messagePattern":"Defined Named (.+?) is a formula, not a range or cell\\.","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Worksheet/Worksheet.php","lineNumber":3450,"sourceCode":"    }\n\n    private function validateNamedRange(string $definedName, bool $returnNullIfInvalid = false): ?DefinedName\n    {\n        $namedRange = DefinedName::resolveName($definedName, $this);\n        if ($namedRange === null) {\n            if ($returnNullIfInvalid) {\n                return null;\n            }\n\n            throw new Exception('Named Range ' . $definedName . ' does not exist.');\n        }\n\n        if ($namedRange->isFormula()) {\n            if ($returnNullIfInvalid) {\n                return null;\n            }\n\n            throw new Exception('Defined Named ' . $definedName . ' is a formula, not a range or cell.');\n        }\n\n        if ($namedRange->getLocalOnly()) {\n            $worksheet = $namedRange->getWorksheet();\n            if ($worksheet === null || $this !== $worksheet) {\n                if ($returnNullIfInvalid) {\n                    return null;\n                }\n\n                throw new Exception(\n                    'Named range ' . $definedName . ' is not accessible from within sheet ' . $this->getTitle()\n                );\n            }\n        }\n\n        return $namedRange;\n    }\n","sourceCodeStart":3432,"sourceCodeEnd":3468,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Worksheet/Worksheet.php#L3432-L3468","documentation":"A DefinedName can wrap a formula (e.g. TaxTotal = '=SUM(B2:B4)*0.2') rather than a cell range. namedRangeToArray() can only convert actual ranges to arrays, so when the resolved name satisfies isFormula() it throws 'Defined Named ... is a formula, not a range or cell.' instead of returning garbage.","triggerScenarios":"namedRangeToArray('TaxTotal') where TaxTotal was created with a formula value (a DefinedName, not a NamedRange); financial or reporting templates from Excel that use named formulas for computed constants.","commonSituations":"Assuming every defined name in an Excel file is a range; mixed templates where some names hold formulas and others hold ranges.","solutions":["Check $defined->isFormula() on the resolved name and branch to a different strategy.","Evaluate named formulas through the calculation engine: Calculation::getInstance($spreadsheet)->calculateFormula($defined->getValue()).","If a range was intended, fix the definition to reference cells (=$B$2:$B$10) using NamedRange."],"exampleFix":"use PhpOffice\\PhpSpreadsheet\\Calculation\\Calculation;\nuse PhpOffice\\PhpSpreadsheet\\DefinedName;\n\n// before\n$data = $sheet->namedRangeToArray('TaxTotal'); // throws: it is a formula\n\n// after\n$defined = DefinedName::resolveName('TaxTotal', $sheet);\nif ($defined !== null && $defined->isFormula()) {\n    $value = Calculation::getInstance($spreadsheet)->calculateFormula($defined->getValue());\n} else {\n    $data = $sheet->namedRangeToArray('TaxTotal');\n}","handlingStrategy":"validation","validationCode":"use PhpOffice\\PhpSpreadsheet\\Calculation\\Calculation;\nuse PhpOffice\\PhpSpreadsheet\\DefinedName;\n\n$defined = DefinedName::resolveName($name, $sheet);\nif ($defined === null) {\n    // missing name: see the 'does not exist' error\n} elseif ($defined->isFormula()) {\n    $value = Calculation::getInstance($spreadsheet)->calculateFormula($defined->getValue());\n} else {\n    $data = $sheet->namedRangeToArray($name);\n}","typeGuard":null,"tryCatchPattern":"use PhpOffice\\PhpSpreadsheet\\Exception as SpreadsheetException;\n\ntry {\n    $data = $sheet->namedRangeToArray($name);\n} catch (SpreadsheetException $e) {\n    if (str_contains($e->getMessage(), 'is a formula')) {\n        // evaluate the named formula instead of arrayifying it\n    } else {\n        throw $e;\n    }\n}","preventionTips":["Inspect isFormula() on every resolved DefinedName before treating it as a range.","Use NamedRange for data ranges and plain DefinedName for formulas so intent stays explicit.","Document which names in your templates are formulas so consumers branch correctly."],"tags":["phpspreadsheet","worksheet","named-range","defined-name","formula"],"backgroundTag":"invalid-defined-name","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}