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

#REF!

#REF!

Error message

#REF!

What it means

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.

Source

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

            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;
    }

    /**
     * INDIRECT.
     *
     * Returns the reference specified by a text string.
     * References are immediately evaluated to display their contents.
     *
     * Excel Function:
     *        =INDIRECT(cellAddress, bool) where the bool argument is optional
     *
     * @param mixed[]|string $cellAddress $cellAddress The cell address of the current cell (containing this formula)
     * @param mixed $a1fmt Expect bool Helpers::CELLADDRESS_USE_A1 or CELLADDRESS_USE_R1C1,
     *                      but can be provided as numeric which is cast to bool
     * @param Cell $cell The current cell (containing this formula)

View on GitHub (pinned to 65b080eef4)

Solutions

1. Guard that the assembled address is a non-empty string before evaluation
2. Substitute defaults for blank components: $addr = $sheetPart . '!' . ($cellPart ?: 'A1')
3. Pre-validate the address with Coordinate::indexesFromString() or a cellref regexp before calling INDIRECT
4. Fail with an application-level message when address parts are missing

Example fix

// before
$address = $prefix . '!' . $suffix; // $suffix empty -> INDIRECT returns '#REF!'

// after
if ($suffix === null || $suffix === '') {
    throw new InvalidArgumentException('INDIRECT address part is missing');
}
$address = $prefix . '!' . $suffix;

if (!is_string($address) || $address === '') {
    throw new InvalidArgumentException('INDIRECT ref_text must be a non-empty string');
}
$result = Indirect::INDIRECT($address, $a1, $cell);

function isNonEmptyString(mixed $value): bool
{
    return is_string($value) && $value !== '';
}

$result = Indirect::INDIRECT($address, $a1, $cell);
if ($result === '#REF!') {
    // ref_text was null/numeric/empty - rebuild the address string
}

When it happens

Trigger: =INDIRECT(A1) where A1 is blank (null); INDIRECT(5); concatenation producing '' ("Sheet2!" & ""); an array ref_text from an array formula.

Common situations: Dynamically built addresses where one component cell is empty; sheet-name concatenation with missing parts; user-typed addresses from an import form.

Related errors

• #VALUE!
• #REF!
• #NUM!
• Invalid parameter passed: formula
• Token with id $id does not exist.

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