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

#VALUE!

#VALUE!

Error message

#VALUE!

What it means

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.

Source

Thrown at src/PhpSpreadsheet/Calculation/LookupRef/Indirect.php:29

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class Indirect
{
    /**
     * Determine whether cell address is in A1 (true) or R1C1 (false) format.
     *
     * @param mixed $a1fmt Expect bool Helpers::CELLADDRESS_USE_A1 or CELLADDRESS_USE_R1C1,
     *                      but can be provided as numeric which is cast to bool
     */
    private static function a1Format(mixed $a1fmt): bool
    {
        $a1fmt = Functions::flattenSingleValue($a1fmt);
        if ($a1fmt === null) {
            return Helpers::CELLADDRESS_USE_A1;
        }
        if (is_string($a1fmt)) {
            throw new Exception(ExcelError::VALUE());
        }

        return (bool) $a1fmt;
    }

    /**
     * Convert cellAddress to string, verify not null string.
     *
     * @param null|mixed[]|string $cellAddress
     */
    private static function validateAddress(array|string|null $cellAddress): string
    {
        $cellAddress = Functions::flattenSingleValue($cellAddress);
        if (!is_string($cellAddress) || !$cellAddress) {
            throw new Exception(ExcelError::REF());
        }

        return $cellAddress;

View on GitHub (pinned to 65b080eef4)

Solutions

1. Pass a real bool: INDIRECT($addr, true) or Helpers::CELLADDRESS_USE_A1 / CELLADDRESS_USE_R1C1
2. Cast string flags before use: (bool) $flag or filter_var($flag, FILTER_VALIDATE_BOOL)
3. Omit the second argument entirely when A1 mode is intended (null defaults to A1)

Example fix

// before
$a1 = $config['a1Mode']; // e.g. "false" from JSON -> '#VALUE!'

// after
$a1 = filter_var($config['a1Mode'] ?? true, FILTER_VALIDATE_BOOL);
$result = Indirect::INDIRECT($address, $a1, $cell);

$a1 = match (true) {
    is_bool($a1fmt) => $a1fmt,
    is_numeric($a1fmt) => (bool) $a1fmt,
    default => true, // null/omitted defaults to A1
};

function isValidA1Flag(mixed $value): bool
{
    return $value === null || is_bool($value) || is_numeric($value);
}

$result = Indirect::INDIRECT($address, $a1, $cell);
if ($result === '#VALUE!' && is_string($a1)) {
    // second argument was a string; cast to bool and retry
}

When it happens

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

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

Related errors

• #VALUE!
• #VALUE!
• #REF!
• #REF!
• ExcelError::throwError($index_number)

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