{"record":{"id":"b9b10df3d3be3002","repo":"PHPOffice/PhpSpreadsheet","slug":"value-b9b10d","errorCode":"#VALUE!","errorMessage":"#VALUE!","messagePattern":"#VALUE!","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/LookupRef/Indirect.php","lineNumber":29,"sourceCode":"use PhpOffice\\PhpSpreadsheet\\Cell\\Coordinate;\nuse PhpOffice\\PhpSpreadsheet\\Worksheet\\Worksheet;\n\nclass Indirect\n{\n    /**\n     * Determine whether cell address is in A1 (true) or R1C1 (false) format.\n     *\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     */\n    private static function a1Format(mixed $a1fmt): bool\n    {\n        $a1fmt = Functions::flattenSingleValue($a1fmt);\n        if ($a1fmt === null) {\n            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;","sourceCodeStart":11,"sourceCodeEnd":47,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/LookupRef/Indirect.php#L11-L47","documentation":"Indirect::a1Format() (Indirect.php:22-33) validates INDIRECT's second argument (A1 vs R1C1): bool and numeric are accepted, null defaults to A1 mode, but any string throws #VALUE!, which INDIRECT() catches and returns as its result. Numeric strings such as \"1\" or \"0\" are rejected too - only real ints/floats/bools pass the is_string() check.","triggerScenarios":"=INDIRECT(\"A1\", \"TRUE\") or INDIRECT(addr, \"0\"); an A1 flag read from a text cell containing 'TRUE'; JSON/config supplying \"a1\": \"false\" straight into the formula or call.","commonSituations":"Config-driven reference building where the flag arrives serialized as text; UI checkboxes serialized as 'true'/'false'; passing the Helper constants as strings instead of bools.","solutions":["Pass a real bool: INDIRECT($addr, true) or Helpers::CELLADDRESS_USE_A1 / CELLADDRESS_USE_R1C1","Cast string flags before use: (bool) $flag or filter_var($flag, FILTER_VALIDATE_BOOL)","Omit the second argument entirely when A1 mode is intended (null defaults to A1)"],"exampleFix":"// before\n$a1 = $config['a1Mode']; // e.g. \"false\" from JSON -> '#VALUE!'\n\n// after\n$a1 = filter_var($config['a1Mode'] ?? true, FILTER_VALIDATE_BOOL);\n$result = Indirect::INDIRECT($address, $a1, $cell);","handlingStrategy":"validation","validationCode":"$a1 = match (true) {\n    is_bool($a1fmt) => $a1fmt,\n    is_numeric($a1fmt) => (bool) $a1fmt,\n    default => true, // null/omitted defaults to A1\n};","typeGuard":"function isValidA1Flag(mixed $value): bool\n{\n    return $value === null || is_bool($value) || is_numeric($value);\n}","tryCatchPattern":"$result = Indirect::INDIRECT($address, $a1, $cell);\nif ($result === '#VALUE!' && is_string($a1)) {\n    // second argument was a string; cast to bool and retry\n}","preventionTips":["Serialize config flags as real booleans, not 'true'/'false' strings","Numeric strings ('1'/'0') are rejected too - only real numbers/bools pass","Omit the second argument when A1 mode is intended"],"tags":["phpspreadsheet","php","excel","indirect","value-error","type-mismatch"],"backgroundTag":"excel-value-error","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}