Download it now!
Bug 90573 - EDITING Entire column/row formula incorrectly changes on row/column deletion
Summary: EDITING Entire column/row formula incorrectly changes on row/column deletion
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.1.0 target:5.0.0.2
Keywords:
Depends on:
Blocks:
 
Reported: 2015-04-11 23:18 UTC by Óvári
Modified: 2016-10-25 19:24 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Óvári 2015-04-11 23:18:50 UTC
1. Open LibreOffice Calc
2. Alt+T (Tools)
3. O (Options)
4. LibreOffice Calc → Formula
5. Formula Options → Formula syntax: Excel A1
6. OK

7. In cell B4, type: =SUM(D:D)
8. Right click on any row (except row 4), say row 15, and 'Delete Selected Rows'
9. The formula in cell B4 should be the same as in point 7 but has now incorrectly changed to: =SUM(D$1:D$1048575)

10. In cell B7, type: =SUM(10:10)
11. Right click on any column (except column B), say column F, and 'Delete Selected Columns'
12. The formula in cell B7 should be the same as in point 10 but has now incorrectly changed to: =SUM($A10:$AMI10)

13. The formula function could be replaced by another (eg. PRODUCT) and the same incorrect change occur.

14. I understand that LibreOffice will have the column and row selection syntax soon. When LibreOffice understands column and row selection the Formula Options → Formula syntax in points 5 could be left as: Calc A1. Hopefully this problem will be solved by then.
Comment 1 raal 2015-04-12 06:50:10 UTC
I can confirm with Version: 4.5.0.0.alpha0+
Build ID: b024e36ddb3b53163d7a01f6f7b5aadb7a858cd9
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2015-03-31_09:12:20

and with LibreOffice 3.5.0  Build ID: d6cde02
Comment 2 m.a.riosv 2015-04-12 22:54:19 UTC
I think a duplicate.

Please if you are not agree reopen it.

*** This bug has been marked as a duplicate of bug 44419 ***
Comment 3 Eike Rathke 2015-06-26 17:42:33 UTC
Not a duplicate, but related.
Comment 4 Eike Rathke 2015-06-26 17:42:49 UTC
Taking.
Comment 5 Commit Notification 2015-06-27 00:01:15 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=dc34bb9b05cf7b5261122a73fa9c6f5356802c19

tdf#90573 do not shift/move entire col/row references and ranges

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 6 Commit Notification 2015-06-27 00:12:32 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b07e0269e5dc266828c4d621d7daae085d644df4&h=libreoffice-5-0

tdf#90573 do not shift/move entire col/row references and ranges

It will be available in 5.0.0.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 7 Óvári 2015-06-27 00:57:17 UTC
Thank you Eike for your work enabling LibreOffice Calc to understand row and column notation.

1. Open LibreOffice Calc
2. Alt+T (Tools)
3. O (Options)
4. LibreOffice Calc → Formula
5. Formula Options → Formula syntax: Calc A1
6. OK

It would be convenient if LibreOffice Calc also knew about the bounds being able to extend to the beginning/end of a column/row.
7. In cell B4, type one of the following: 
=SUM(D10:D)
=SUM(D$10:D)
=SUM($D$10:$D)
=SUM(D$10:D)
=SUM(D:D10)
=PRODUCT(15:H15)
=PRODUCT(H12:H)

This will enable spreadsheets created with LibreOffice Calc to be robust if the number of rows/columns in varied in the future.

This can also assist LibreOffice Calc's functions, i.e. VLOOKUP(), if there is information at the beginning of the column/row to only search in a subset of a column/row.

If I understand correctly, Microsoft Office Excel has a feature where you can use the API to create spreadsheet with a number of cells greater than that which can be opened directly with Excel program.
Comment 8 Eike Rathke 2015-06-29 09:17:56 UTC
That would be different feature request and not related to this specific bug. I'm setting this to RESOLVED again.

However, I doubt we'd implement such special case references, given that not even Excel understands them. Bear in mind they'd have to be stored as D10:D1048576 in most file formats anyway.