{"record":{"id":"6b62f6052c9f219e","repo":"PHPOffice/PhpSpreadsheet","slug":"ref","errorCode":"#REF!","errorMessage":"#REF!","messagePattern":"#REF!","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/LookupRef/Indirect.php","lineNumber":44,"sourceCode":"            return Helpers::CELLADDRESS_USE_A1;\n        }\n        if (is_string($a1fmt)) {\n            throw new Exception(ExcelError::VALUE());\n        }\n\n        return (bool) $a1fmt;\n    }\n\n    /**\n     * Convert cellAddress to string, verify not null string.\n     *\n     * @param null|mixed[]|string $cellAddress\n     */\n    private static function validateAddress(array|string|null $cellAddress): string\n    {\n        $cellAddress = Functions::flattenSingleValue($cellAddress);\n        if (!is_string($cellAddress) || !$cellAddress) {\n            throw new Exception(ExcelError::REF());\n        }\n\n        return $cellAddress;\n    }\n\n    /**\n     * INDIRECT.\n     *\n     * Returns the reference specified by a text string.\n     * References are immediately evaluated to display their contents.\n     *\n     * Excel Function:\n     *        =INDIRECT(cellAddress, bool) where the bool argument is optional\n     *\n     * @param mixed[]|string $cellAddress $cellAddress The cell address of the current cell (containing this formula)\n     * @param mixed $a1fmt Expect bool Helpers::CELLADDRESS_USE_A1 or CELLADDRESS_USE_R1C1,\n     *                      but can be provided as numeric which is cast to bool\n     * @param Cell $cell The current cell (containing this formula)","sourceCodeStart":26,"sourceCodeEnd":62,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/LookupRef/Indirect.php#L26-L62","documentation":"Indirect::validateAddress() (Indirect.php:40-48) requires ref_text to be a non-empty string after flattenSingleValue(); anything else - null, numbers, arrays, '' - throws #REF!, which INDIRECT() returns as its result. Excel itself coerces more inputs, so code that 'worked' as a raw Excel formula can fail once the address is assembled in PHP.","triggerScenarios":"=INDIRECT(A1) where A1 is blank (null); INDIRECT(5); concatenation producing '' (\"Sheet2!\" & \"\"); an array ref_text from an array formula.","commonSituations":"Dynamically built addresses where one component cell is empty; sheet-name concatenation with missing parts; user-typed addresses from an import form.","solutions":["Guard that the assembled address is a non-empty string before evaluation","Substitute defaults for blank components: $addr = $sheetPart . '!' . ($cellPart ?: 'A1')","Pre-validate the address with Coordinate::indexesFromString() or a cellref regexp before calling INDIRECT","Fail with an application-level message when address parts are missing"],"exampleFix":"// before\n$address = $prefix . '!' . $suffix; // $suffix empty -> INDIRECT returns '#REF!'\n\n// after\nif ($suffix === null || $suffix === '') {\n    throw new InvalidArgumentException('INDIRECT address part is missing');\n}\n$address = $prefix . '!' . $suffix;","handlingStrategy":"type-guard","validationCode":"if (!is_string($address) || $address === '') {\n    throw new InvalidArgumentException('INDIRECT ref_text must be a non-empty string');\n}\n$result = Indirect::INDIRECT($address, $a1, $cell);","typeGuard":"function isNonEmptyString(mixed $value): bool\n{\n    return is_string($value) && $value !== '';\n}","tryCatchPattern":"$result = Indirect::INDIRECT($address, $a1, $cell);\nif ($result === '#REF!') {\n    // ref_text was null/numeric/empty - rebuild the address string\n}","preventionTips":["Validate every concatenated component before building a reference","Treat missing sheet/cell parts as application errors, not formula errors","Remember PhpSpreadsheet is stricter than Excel here: numbers are not coerced to addresses"],"tags":["phpspreadsheet","php","excel","indirect","ref-error","empty-string"],"backgroundTag":"excel-ref-error","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}