Bug 131938 - If a data range is defined in a document, it is partly translated in formulas into the normal cell range after saving and re-opening the document
Summary: If a data range is defined in a document, it is partly translated in formulas...
Status: REOPENED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.0.0.0.alpha0+
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Name
  Show dependency treegraph
 
Reported: 2020-04-06 18:07 UTC by Jürgen Kirsten
Modified: 2023-10-03 19:08 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Example document to reproduce the bug (22.14 KB, application/zip)
2020-04-06 18:08 UTC, Jürgen Kirsten
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jürgen Kirsten 2020-04-06 18:07:17 UTC
Description:
If a data range is defined in a .ods document, it is in formulas partly translated into the normal cell range (A1:A30) after saving and re-opening the document. 
This happens in formulas in which only a part of the data range has been addressed. So in my case with 'data[columnA]' a column of my data range. If you then want to change the data range, this does not work for the previously defined part of the data range. Formulas with the whole data range (in my case 'data') survive the save and reopen process.
If you save the document in .xlsx the data part of the data range is kept. This is also the reason why I uploaded the example document in .xlsx. So you can see the before/after effect. Please open the document and save it as .ods. Note the differences in the cells d4, d7 and d10.
It does not matter if the data range is connected to a data source. There the effect is the same.

Also I am annoyed that I address the whole range with data also with column headers. See formula COUNTA.
With the exact specification of the column it is done correctly in my eyes. It is counted without column headers.

Steps to Reproduce:
1. Define Range: a Cell range in your sheet
2. write a formula like COUNTA with the name of the range with the name of the column. E.g. data[columnA]
3. save your document ind ods format.
4. Re-open the document


Actual Results:
The formula is translated to cell area like A1:A30

Expected Results:
still the name from the data range with the name of the column


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 7.0.0.0.alpha0+ (x64)
Build ID: d43d2662ff64c613740080e166d1046214edb0f0
CPU threads: 8; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-GB
Calc: threaded
Comment 1 Jürgen Kirsten 2020-04-06 18:08:39 UTC
Created attachment 159369 [details]
Example document to reproduce the bug
Comment 2 m_a_riosv 2020-04-06 21:37:29 UTC
You can save that as ODF it is not defined on it, and not implemented, it's only on xlsx documents for compatibility.

https://wiki.documentfoundation.org/ReleaseNotes/5.0#Table_structured_references
Comment 3 m_a_riosv 2020-04-06 21:38:03 UTC
Sorry "can't save as ODF"
Comment 4 Jürgen Kirsten 2020-04-12 17:03:53 UTC
So I reopen this and changed into a enhancement.

The structured references are currently not saved in ODF format. Please change this. This would also lead to a better acceptance by the old Excel users.
The data range is already saved correctly today. Only if I refer to a column 'Database[Row]' this is translated into the cell range when saving in *.ods. 
In the Release Notes for 5.0 it says that this also happens with *.xls. This is also correct. But with *.xlsx I can save and my structured references are preserved.
Do I always have to save my documents in *.xlsx?
Comment 5 Jürgen Kirsten 2020-04-12 17:10:49 UTC
Sorry, it must of course be called 'Database [column]'.
I do not speak English as a native language.
Comment 6 m_a_riosv 2020-04-12 18:26:37 UTC
https://bugs.documentfoundation.org/show_bug.cgi?id=85063#c27 has a note of dev about this.

In any case better open a new bug with a clear title about.
I think it is already but I can't found it.