Confused about named ranges/defined names

[Matt Jadud]

Hi all,

I had code that successfully generated defined names; I think I was attaching them to sheets. I've decided I'd rather they were global names. But, now, I cannot see/find the names.

I'm certain I'm missing something simple.

I'm using Python 3.12, and OpenPyxl 3.1.5. 

The essence of my code is here. It is pulling data out of a JSON object. The variables used for constructing the DefinedName all have values/are valid, asserted by processes that took place before they got to this code.

```

    range_name = dn["name"]
    range = dn["rangeReference"]
    start = range["start"]
    end = range["end"]
    sheetName = range["sheetName"]

    ref = f"{quote_sheetname(sheetName)}!{absolute_coordinate(start + ':' + end)}"
    logger.info(f"[DN] {range_name} {ref}")

    DN = DefinedName(range_name, attr_text=ref)
    wb.defined_names[range_name] = DN

```

I'm confident those values are all present/valid. My debug statements say things like:

```

[DN] version 'Coversheet'!$B$2:$B$2

```

And later in my process I check that I have named ranges:

```

        dests = wb.defined_names
        for _sh, _rng in dests.items():
            logger.debug(f"{_sh}: {_rng}")

```

which spits out what I would expect. Things that look like:

```

DEBUG    wbc.components.workbook:workbook.py:48 total_amount_expended: <openpyxl.workbook.defined_name.DefinedName object>
Parameters:

name='total_amount_expended', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text="'Coversheet'!$B$5:$B$5"

```

Finally, I looked at the XML for the workbook. In `workbook.xml`, I find:

```

  <definedNames>
    <definedName name="version">'Coversheet'!$B$2:$B$2</definedName>
    <definedName name="section_name">'Coversheet'!$B$3:$B$3</definedName>
    <definedName name="total_amount_expended">'Coversheet'!$B$5:$B$5</definedName>
  </definedNames>

```

However, when I open these in LibreOffice (7.3,7.2, Ubuntu 24.04), I see no ranges. Likewise, Google Docs. I'm unclear what I'm missing at this point.

Any thoughts on what I'm doing wrong/missing? Is it that I'm generating the defined names incorrectly? Is it Libre Office that is not presenting them?

Many thanks,

Matt

[Matt Jadud]

So.

If you name your sheet one thing in your test case, and then a range references a differently named sheet (which is not in the workbook), then the named ranges won't appear.

E.g. If the sheet was named "SheetOne," and you declare the (global) named range to reference "Sheet1!A1:A5", then nothing will work.

I made some changes, and my test cases were no longer valid. Nothing to see here... move along, move along...

All of that said, are PRs against the docs acceptable? Any guidelines/preferences to how it is done? I would be willing to expand out the defined names section.

Cheers,

Matt

[Charlie Clark]

On 30 Aug 2025, at 20:22, Matt Jadud wrote:

So.

If you name your sheet one thing in your test case, and then a range
references a differently named sheet (which is not in the workbook), then
the named ranges won't appear.

Yes, the specification is basically shit.

E.g. If the sheet was named "SheetOne," and you declare the (global) named
range to reference "Sheet1!A1:A5", then nothing will work.

Checking this is what I consider "application-like" functionality and beyond the scope of Openpyxl.

I made some changes, and my test cases were no longer valid. Nothing to see
here... move along, move along...

All of that said, are PRs against the docs acceptable? Any
guidelines/preferences to how it is done? I would be willing to expand out
the defined names section.

PRs are always welcome. The workflow with Heptapod is a bit different in that you can't fork the library, you make a "topic", which is a kind of branch and the rest is the same.

However, you need to be a developer to do this and I'm away from keyboard for the next couple of weeks.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226