Named ranges disappear in Calc

English
#27

Hi, maybe a workaround. I recently had this issue, am having this issue, where named ranges / database named ranges just disappeared, lost a stack of named ranges, but 4 stayed, don’t know why. Cell references change to #NAME? and don’t magically return to normal when the named ranges are added back.

Here’s what I did: More carefully handling the contents.xml file got a result for me, putting the named ranges back into my calc workbook. That is, changing calc sheet from .ods file extension to .zip, extracting the content.xml file, extremely carefully copying and pasting the named ranges section of the xml file from another sheet [that had most but not all the namedranges] into this extracted content.xml file [everything between the table:named-expressions/ and </table:database-ranges> tags, saving it, copying it back into the .zip package replacing the existing copy there, then renaming the extension back from .zip to .ods.
This put the named ranges back BUT as you all know the cell references were still damaged, referencing #NAME? where each named range was. By using FileCompare, EditMenu → TrackChanges → CompareDocument, and comparing to another earlier copy of the file [same one as i got the content.xml tag section from], it somehow found the changes to cell formula when the sheet lost the named ranges. Using the filter in the file comparison dialog to narrow to #NAME?, i was able to undo many of those changes, reverting back to correct formula for many, not all, of the unexpected screwing up of named ranges. !!! Remember to do this on copies of the .ods files… just in case.

Also, being slightly a noob[ish] to libreoffice calc’s features on this front, i had no idea calc has 2 very separate but confusingly similar ways of naming ranges… both called fairly similarly, in my mind, named ranges and named database ranges… this is a bit weird when coming from my background… it confused me, doh, exacerbated by panicking about the weird named ranges issue and my main sheets getting screwed up. Weirdly too, this named range issue just magically jumped files and occurred in another file too… an earlier saved version of the calc file with the issue.

Anyhoo, this approach may help a little hopefully, a kinda indirect working fix at least, that doesn’t require rebuilding everything from near scratch.

Also, sorry for my ignorance, how could we generate a list of all database named ranges too please? the code in this and other threads is for named ranges only…

Very best to everyone, jb

1 Like
#28

John, this sounds like a great way to attempt a recovery, thank you for sharing the details!

I decided the best way to avoid this is to never again use named ranges in LibreOffice. If I need to reference cells that are not nearby or on another sheet, I’ll use a different product. I copied everything that still worked to a new file, and simply said goodbye to the now useless formulas I developed over several years. I might have tried your approach if I had seen it sooner, but I’ve moved on. It’s reassuring to see that I’m not the only one seeing this problem, but disappointing that such a valuable feature is not reliable. I would certainly never use LO for anything critical. For small personal sheets it’s still good, when combined with frequent backups.

1 Like
#29

It is great that you shared the steps!

Note though, that the most important thing in this issue is not how to recover, but rather, how to fix the program to prevent this from happening at all. And to allow that, a problematic file before the recovery would be nice and maybe helpful to allow developers to see the immediate file structure damage; sometimes that’s already enough to find the code problem. And even greater would be exact steps to reproduce the problem - like “this happens for me on this file; if I open it, add that, change that this way, and save, it breaks”.

4 Likes
#30 
Sub ShowNamesOfAllDBRanges()
	Dim i%, dbrg, s$

	With ThisComponent.DatabaseRanges
		For i = 0 To .Count - 1
			dbrg = .getByIndex(i)
			s = s & Chr$(10) & dbrg.Name
		Next
	End With
	If Len(s) > 0 Then
		MsgBox "All Database Range Names:" & Chr(10) & Mid(s, 2) _
		 , MB_ICONINFORMATION, Title:=ThisComponent.Title
	Else
		MsgBox "No database ranges found." _
		 , MB_ICONINFORMATION, Title:=ThisComponent.Title
	End if
End Sub

I actively use them and can not imagine how you can effectively organize data without them. Your judgments are hasty. In addition, this problem is not widespread (it is not written about).

2 Likes
#31

Thanks very much indeed! I’ve not been able to find object hierarchies that i can make sense of and code examples like the one you’ve kindly just given, thanks very much Eeigor!

#32

That’s very unfortunate, but you’ve gotta be comfortable using the program/s. Very best to you MrDave, jb

#33

Hi again MikeK! Ah yes, good point. Thank you for your good guidance again, sorry about that. The .ods files with the issues contain critically private data… even the named ranges and database ranges give away private details. So I’m not sure how i can expunge/change the data and namedranges and not get rid of the issue/corruption. It may have been some corruption to the file or parts of the content.xml file in particular? Hard to know without example files and reproducible steps at your end, sorry.
I have a flu at mo, so will see if i can strip private data et al and keep the issue, when i’m more recovered…
Just for interest’ sake though, After copy/pasting info from content.xml files to get nemedranges back, turns out they’re mosttly database named ranges, the issue still happened, i narrowed down one of the triggers, when i deleted a copy of one of the sheets/tabs (not the main data sheet, A Copy of it], then the named ranges would disappear again… even renaming that sheet/copy didn’t matter, deleting it killed the named ranges. Then after manually deleting a bulk of named ranges and named database ranges, i could delete the copy sheet and several other sheets/tabs without triggering the issue. Very interesting. I’ve manually readded ranges and recreated formula… all seems fine now…

#34

Interesting. It sounds like when you delete a copy of a sheet, the same named ranges from original sheet get deleted also. I wonder if those named ranges (or database ranges) were global, or was the scope specific to the sheet. That might be what caused me to lose so many named ranges but I can’t be sure, that was weeks (months?) ago.

1 Like
#35

No.
Created a Calc document. Entered data and named the range. The name is global (in the document). Created a copy of the sheet. Calc added the same name, but local (on the sheet). Deleted the copy of the sheet. In the Manage Names dialog, one global name remains. However, both names are still present in the drop-down list of the Name Box field. The field is not updated. There is another complaint about this field: it sorts names case-sensitive, while the Manage Names dialog is not case-sensitive.
A message appears when you try to select a removed range.
Снимок экрана от 2022-08-09 10-53-59

#36

Please submit a bug for that.

1 Like
#37

https://bugs.documentfoundation.org/show_bug.cgi?id=150307

2 Likes
#38

https://bugs.documentfoundation.org/show_bug.cgi?id=150312

4 Likes
#39

Forgot to mention, in case this helps anyone? the content.xml in the section for namedranges named databaseranges had additional named database ranges that do not appear in the calc gui dialogs for named database ranges:
<table:database-range table:name="__Anonymous_DB__1" table:target-range-address=“Transactions…
<table:database-range table:name=”__Anonymous_DB__2"…
<table:database-range table:name="__Anonymous_Sheet_DB__0"…
<table:database-range table:name="__Anonymous_Sheet_DB__1" …

So you’d only know they’re there by looking at the xml, they don’t show up in the output from Eeigor’s ShowNamesOfAllDBRanges() code either… They were still in the .ods i manually fixed too, until i removed them from the content.xml manually too.

Curiouser and curiouser cried Alice…

#40

Those anonymous DB ranges are automatically created one per sheet for whenever an AutoFilter or Sort is used on a sheet and no defined DB range was used and remember the settings, as such consider them temporary but stored. They are not supposed to be shown in the UI anywhere, nor are they included in the enumeration of DB ranges because they are handled only internally and not under user control. They can be accessed using the css::sheet::XUnnamedDatabaseRanges interface.

2 Likes
Strange behavior of resetting filter with macro
#41

@erAck, I have to use it because of the missing property (there is one in Excel):

Function GetAutoFilterMode(Optional oDoc As Object, Optional nSheet%) As Boolean
'''	Returns: True if the AutoFilter drop-down arrows are currently displayed on the sheet.

	On Local Error GoTo Failed
	Dim oUnnamedDBRanges As Object  'unnamed (anonymous) database ranges

	If IsMissing(oDoc) Then oDoc = ThisComponent
	If IsMissing(nSheet) Then
		nSheet = oDoc.CurrentController.ActiveSheet.RangeAddress.Sheet
	End If
	oUnnamedDBRanges = oDoc.getPropertyValue("UnnamedDatabaseRanges")
	With oUnnamedDBRanges
		GetAutoFilterMode = .getByTable(nSheet).AutoFilter  'table -> ScDatabaseRangeObj
	End With

Failed:
End Function
#48

For me the problem is not solved at all.
Ubuntu 20.04, Libreoffice 7.6.2

It happened to me recently and many times on a large workbook with about 50 named ranges.

Each time it happened, I could not precisely determine when, how nor why because values in all cells are stored in file and are not recalculated again unless an ancestor cell changed or I explicitly ask to recompute all (Ctrl+Shift+F9 I think).
So each time I realize my file is corrupted, it is too late to investigate further…

Fortunately, I do not update formulae, only values.
So I copy my new values from the corrupted (newer) file to a backup (older) file…

I decided today to downgrade to latest 6.x.x version.
hopefully it can solve (temporarily) the problem.

#42

I don’t much about Calc, but I had a similar problem and found that Calc deleted a RANGE from the list when I deleted the cells in that RANGE. When I CLEARED the cells (instead of DELETING them) there was no longer a problem.

#44

When you use a formula such as =SUM(A1:A8) and you remove cells A1:A8, the formula becomes =SUM(A#REF!:A#REF!) because the referenced cells do not exist anymore. The same happens tp any named range where the cells have been removed.
This is expected spreadsheet behaviour since decades. The application tries to keep track of references when you insert cells, remove cells or when you move cells to another place.
If you don’t want this tracking for some reason, there are ways to evade the feature. For instance =SUM(INDIRECT(“A1:A8”)) sums the range that is described by the address string “A1:A8” and converted to a reference by the INDIRECT function.

#45

Villeroy, I did not mean to imply that there was any problem with CALC. I encountered the unexpected behavior when RECORDING Macros. At the time, I did not know how to ‘clear the cells’, so I deleted them. I now know much better. I just thought that anybody else experiences this unexpected behavior, they might check to see that they may have accidentally deleted the cells in the range, thus causing CALC to remove the RANGE from the list…

#46

It is still in the list of named ranges [Ctrö+F3]. In the attached document I named A1:A8 as “Named” and then deleted A1:A8. “Named” is still in the list of named ranges and in the name box left of the formula bar. It vanished from the navigator.
named_cells_deleted.ods (10.7 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.