iOfficeAI/OfficeCLI · error · ArgumentException

Defined-name ref '{refVal}' has no sheet qualifier — Excel r

Error message

Defined-name ref '{refVal}' has no sheet qualifier — Excel refuses unqualified cell ranges in defined names. Use ref=SheetName!A1:B1, or add via the sheet path (add <file> /Sheet1 --type namedrange ...).

What it means

Thrown when the ref value is a bare A1-style range with NO sheet qualifier (`!`) AND the parent path does not name a valid sheet to auto-qualify with. Excel refuses unqualified cell ranges inside a defined-name body (0x800A03EC). The code tries to auto-qualify using the parent path's first segment if it is an existing worksheet (and not 'namedrange'/'workbook'); only if that rescue fails does it throw. So this fires specifically when neither the ref nor the path supplies a sheet.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs:127

        // A bare A1-style range with no sheet qualifier is also INVALID in a
        // defined-name body — Excel refuses the whole file (0x800A03EC).
        // When the parent path names a sheet (add /Sheet1 --type namedrange),
        // qualify with it; otherwise the ref is ambiguous and rejected.
        if (!refVal.Contains('!')
            && System.Text.RegularExpressions.Regex.IsMatch(refVal.Replace("$", ""),
                @"^[A-Za-z]{1,3}\d+(:[A-Za-z]{1,3}\d+)?$"))
        {
            var nrParentSheet = parentPath.TrimStart('/').Split('/', 2)[0];
            if (!string.IsNullOrEmpty(nrParentSheet)
                && !nrParentSheet.StartsWith("namedrange", StringComparison.OrdinalIgnoreCase)
                && !nrParentSheet.Equals("workbook", StringComparison.OrdinalIgnoreCase)
                && FindWorksheet(nrParentSheet) != null)
            {
                refVal = $"{Core.ModernFunctionQualifier.QuoteSheetNameForRef(nrParentSheet)}!{refVal}";
            }
            else
            {
                throw new ArgumentException(
                    $"Defined-name ref '{refVal}' has no sheet qualifier — Excel refuses unqualified cell ranges " +
                    "in defined names. Use ref=SheetName!A1:B1, or add via the sheet path (add <file> /Sheet1 --type namedrange ...).");
            }
        }
        ValidateDefinedNameRef(refVal);

        var workbook = GetWorkbook();
        // CONSISTENCY(workbook-child-order): helper inserts <definedNames>
        // in schema-correct position (before calcPr/oleSize/...).
        var definedNames = GetOrCreateDefinedNames(workbook);

        var dn = new DefinedName(refVal) { Name = nrName };

        // Scope resolution: explicit --prop scope= wins. Otherwise, if the
        // parentPath addresses a specific sheet (e.g. "/Sheet1"), default
        // the scope to that sheet — callers who picked a sheet-level
        // parent almost always wanted a sheet-scoped name, and previously
        // the parentPath was silently ignored and the name landed at

View on GitHub (pinned to 1ced45e900)

Solutions

  1. Qualify the range with a sheet: `--prop ref=Sheet1!A1:B1`.
  2. Or add via a sheet path so it auto-qualifies: `add ./book.xlsx /Sheet1 --type namedrange --prop name=X --prop ref=A1:B1`.

Example fix

// before
add ./book.xlsx /namedrange --type namedrange --prop name=X --prop ref=A1:B1
// after
add ./book.xlsx /namedrange --type namedrange --prop name=X --prop ref=Sheet1!A1:B1
Defensive patterns

Strategy: validation

Validate before calling

// Ensure every bare A1 range in a defined name carries a sheet qualifier.
var bareRange = @"^[A-Za-z]{1,3}\d+(:[A-Za-z]{1,3}\d+)?$";
if (!refVal.Contains('!') && Regex.IsMatch(refVal.Replace("$",""), bareRange)
    && !CanAutoQualifyFromPath(parentPath))
    throw new InvalidOperationException(
        $"Defined-name ref '{refVal}' needs a sheet qualifier: use SheetName!A1:B1");

Type guard

static bool RefHasSheetQualifierOrIsNotBareRange(string refVal)
    => refVal.Contains('!')
    || !System.Text.RegularExpressions.Regex.IsMatch(
        refVal.Replace("$",""), @"^[A-Za-z]{1,3}\d+(:[A-Za-z]{1,3}\d+)?$");

Try / catch

try { handler.AddNamedRange(...); }
catch (ArgumentException ex) when (ex.Message.Contains("no sheet qualifier"))
{ /* re-issue with SheetName! prefixed, or via a sheet path */ }

Prevention

When it happens

Trigger: Calling `add /namedrange --type namedrange --prop name=X --prop ref=A1:B1` (path is the generic `namedrange`, not a sheet, so no auto-qualify) or `add /workbook --type namedrange --prop ref=A1`. Supplying `ref=Sheet1!A1:B1` avoids it; supplying a sheet path like `/Sheet1` auto-qualifies the bare range.

Common situations: Adding a workbook-scope name with a bare cell range forgetting that defined names require an explicit sheet; assuming the active sheet is implied (it is not in OOXML defined names).

Related errors

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