| Summary: | Absolute references change when deleting/inserting rows/columns | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Zarko Zivanov <zzarko> |
| Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | RESOLVED NOTABUG | ||
| Severity: | normal | CC: | miguelangelrv |
| Priority: | medium | ||
| Version: | 5.1.3.2 release | ||
| Hardware: | x86-64 (AMD64) | ||
| OS: | Linux (All) | ||
| Whiteboard: | |||
| Crash report or crash signature: | Regression By: | ||
| Attachments: | spreadsheet with data illustrating the problem | ||
Hi Zarko, Relative/absolute references have there main meaning for copy. If you delete a row that is in the middle of a referenced range, it's right the range is adjusted, the range has one less row and relative/absolute references has no effect on how this works. You can solve with OFFSET() function https://help.libreoffice.org/index.php?title=Calc/Spreadsheet_Functions&Language=en-US&System=WIN&Version=5.1#bm_id3148926 or with INDIRECT() https://help.libreoffice.org/index.php?title=Calc/Spreadsheet_Functions&Language=en-US&System=WIN&Version=5.1#bm_id3153922 but both are volatile functions, so if they are used intensively can slow down the calculation. Another option is take advantage of a limitation with named ranges, for those relative references are not adapted if you delete a row or a column in the range. Please if you have more question about, do it in ask.libreoffice.org Thank you very much for your answer and sorry again for spending your time. |
Created attachment 125158 [details] spreadsheet with data illustrating the problem First, I'm not sure if this is a bug or a expected behaviour and I'm sorry if I wasted your time... If I have absolute reference in a formula, and then delete rows or columns that are part of cell range defined by absolute references, the formula changes, the range defined by absolute references gets smaller. Similar for inserting rows/columns. In the example spreadsheet, there is a column and a row of data, and two formulas in B1 and C1, summing the data using absolute references. If I delete one or more rows containing data (right-click on selected rows, Delete rows), the formula in B1 changes, for example if I delete rows 10-12, the formula changes from =SUM($A$4:$A$20) to =SUM($A$4:$A$17). I was expecting for this to happen to non-absolute references, but not with absolute ones. And I could be wrong for expecting this. If this isn't a bug, but expected behaviour, is there a way to make references constant, so they don't change when deleting/inserting columns/rows?