qax-os/excelize · error

invalid reference

Error message

invalid reference

What it means

During range/reference normalization, the code adjusts a cellRange against parsed bounds and requires that both the From and To endpoints belong to the same sheet as the parsed cell reference. If they differ, it returns errors.New("invalid reference"). This is a plain Go error (no Excel error code), raised for cross-sheet or malformed ranges where one endpoint's sheet does not match.

Source

Thrown at calc.go:1631

		}
		return cr, false, false, err
	}
	return cr, false, false, err
}

// prepareCellRange checking and convert cell reference to a cell range.
func (cr *cellRange) prepareCellRange(col, row bool, cellRef cellRef) error {
	if col {
		cellRef.Row = TotalRows
	}
	if row {
		cellRef.Col = MaxColumns
	}
	if cellRef.Sheet == "" {
		cellRef.Sheet = cr.From.Sheet
	}
	if cr.From.Sheet != cellRef.Sheet || cr.To.Sheet != cellRef.Sheet {
		return errors.New("invalid reference")
	}
	if cr.From.Col > cellRef.Col {
		cr.From.Col = cellRef.Col
	}
	if cr.From.Row > cellRef.Row {
		cr.From.Row = cellRef.Row
	}
	if cr.To.Col < cellRef.Col {
		cr.To.Col = cellRef.Col
	}
	if cr.To.Row < cellRef.Row {
		cr.To.Row = cellRef.Row
	}
	return nil
}

// parseReference parse reference and extract values by given reference
// characters and default sheet name.

View on GitHub (pinned to f2483381fb)

Solutions

1. Rewrite the formula so both range endpoints are on the same sheet
2. Explicitly qualify the sheet consistently, e.g. Sheet1!A1:Sheet1!B2
3. If constructing cellRef/cellRange in code, set Sheet identically on From and To
4. Split cross-sheet computations into separate same-sheet formulas or handle them in Go code

Example fix

// before: formula =SUM(Sheet1!A1:Sheet2!B2) -> invalid reference
// after: =SUM(Sheet1!A1:Sheet1!B2) plus a separate =SUM(Sheet2!A1:Sheet2!B2)

// ensure range endpoints share one sheet before evaluating
// reject cross-sheet ranges like Sheet1!A1:Sheet2!B2
if strings.Count(formula, "!") > 1 && strings.Contains(formula, ":") {
    return fmt.Errorf("cross-sheet range not supported")
}

if _, err := f.Calculate(); err != nil {
    if err.Error() == "invalid reference" {
        // rewrite formula with consistent sheet qualification
    }
    return err
}

When it happens

Trigger: Calling APIs that resolve ranges (formula evaluation involving A1:B2 style references, e.g. via GetCellValue-driven formula recalculation or parseReference paths) where the range endpoints reference different sheets, like Sheet1!A1:Sheet2!B2, or where the From.Sheet was not set as expected.

Common situations: 3-D or cross-sheet range formulas (Sheet1:Sheet3!A1) that excelize does not support; programmatically constructed cellRange structs with inconsistent Sheet fields; copied formulas whose sheet qualifier was lost.

Related errors

• #NAME?
• #VALUE!
• #DIV/0!
• #NUM!
• coordinates length must be 4

AI-assisted analysis of qax-os/excelize@f2483381fb (2026-09-02). Data as JSON: /api/errors/9d24886af718f061. Report an issue: GitHub.