iOfficeAI/OfficeCLI · error · ArgumentException

Cross-workbook references like '{formula}' require an extern

Error message

Cross-workbook references like '{formula}' require an externalLinks part which officecli doesn't expose; use raw-set for this case

What it means

A formula begins with a cross-workbook reference — either a numeric workbook index like [1]Sheet1!A1 or a filename+extension like [Other.xlsx]Sheet1!A1. Such references need an externalLinks part to resolve, which officecli does not expose. Without it, Excel opens the file but the cell shows #REF!, so the library rejects up-front rather than persisting a broken formula.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs:364

    /// </summary>
    internal static void RejectCrossWorkbookFormula(string formula)
    {
        if (string.IsNullOrEmpty(formula)) return;
        var trimmed = formula.TrimStart('=', ' ', '\t');
        // CONSISTENCY(cross-workbook-vs-structured-ref): the older `^\[` guard
        // also matched OOXML structured table references like `[@Price]` and
        // `[Price]*[Qty]`, falsely rejecting valid Excel-365 formulas. Real
        // cross-workbook refs have one of two shapes:
        //   - numeric workbook index:  `[1]Sheet1!A1`        → `[<digits>]`
        //   - filename + extension:    `[Other.xlsx]Sheet!A1` → `[<name>.xls(x|m|b)?]`
        // Both forms are followed by a sheet reference (`Sheet!...`), but the
        // bracket payload alone is enough to disambiguate from `[@Col]` /
        // `[Col]` structured refs (which contain `@`, alphabetics without an
        // extension, or `:`).
        if (System.Text.RegularExpressions.Regex.IsMatch(trimmed,
                @"^\[(\d+|[^\]]*\.xls[xbm]?)\]",
                System.Text.RegularExpressions.RegexOptions.IgnoreCase))
            throw new ArgumentException(
                $"Cross-workbook references like '{formula}' require an externalLinks part which officecli doesn't expose; use raw-set for this case");
    }

    // Normalize user-supplied data-validation formula values so Excel accepts
    // them. `type=list` auto-quotes bare lists. `type=time` accepts HH:MM /
    // HH:MM:SS and converts to the Excel time serial fraction. `type=date`
    // accepts YYYY-MM-DD and converts to the Excel date serial. `type=custom`
    // strips a leading '=' since OOXML `<x:formula1>` expects the formula body
    // without one.
    internal static string NormalizeValidationFormula(string value, DataValidationValues? type)
    {
        if (string.IsNullOrEmpty(value)) return value;
        if (type == DataValidationValues.List)
        {
            // list: wrap bare "a,b,c" in quotes; leave cell/range refs and
            // already-quoted literals alone. V1: a leading `=` signals a
            // formula-ref (e.g. `=VOpts`, `=$Z$1:$Z$5`) — strip the `=`
            // (OOXML `<x:formula1>` expects the body without one) and

View on GitHub (pinned to 1ced45e900)

Solutions

  1. Use the raw-set escape hatch if cross-workbook resolution is genuinely required (the message names it).
  2. Inline the referenced data into the current workbook and re-point the formula at the local range.
  3. Replace the external ref with a value paste if live linking is not needed.

Example fix

// before
sheet.SetFormula("A1", "=[Other.xlsx]Sheet1!B2");

// after (option A: local ref)
sheet.SetFormula("A1", "=Sheet1!B2");
// after (option B: raw-set if you manage externalLinks yourself)
sheet.RawSetFormula("A1", "=[Other.xlsx]Sheet1!B2");
Defensive patterns

Strategy: validation

Validate before calling

static readonly Regex CrossWb =
    new(@"^\[(\d+|[^\]]*\.xls[xbm]?)\]", RegexOptions.IgnoreCase);
static bool IsCrossWorkbook(string formula) =>
    CrossWb.IsMatch((formula ?? string.Empty).TrimStart('=').Trim());

Try / catch

try { sheet.SetFormula(cell, formula); }
catch (ArgumentException ex) when (ex.Message.Contains("Cross-workbook references")) {
    sheet.RawSetFormula(cell, formula);   // only if you manage externalLinks yourself
}

Prevention

When it happens

Trigger: Setting a formula whose body starts with =[<digits>] or =[<name>.xls(x|m|b)?] per the IsMatch regex. The bracket payload alone disambiguates from structured refs like [@Col] or [Col]*[Qty].

Common situations: Copying formulas out of a workbook that had live external links; expecting the tool to wire up externalLinks automatically; migrating from a multi-file model.

Related errors

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/cd2b383a9f6d635b. Report an issue: GitHub.