Description: In a spreadsheet with 5 sheets, 1 small and 4 of 1000 lines x ca. 50 columns each, when I use the OFFSET command in a cell, save and reopen the file, it starts with Calc having recognized changes that are to be saved. I delted the entire row with OFFSET, saved and reopened the file: Everything ok, Calc does not quote me to save the file, no changes detected. I re-entered the OFFSET command into a single cell, saved, reopened and: The save button again says that there have been made changes that need to be saved. - I tried to reproduce it with a fresh sheet where the only thing I entered, was the OFFSET command, saved, reopened: No error. - I delted all sheets and other lines in my big sheet, leaving only the row with the OFFSET command, saved, reopened: No error Steps to Reproduce: 1. Open file 2. 3. Actual Results: File opens with changes to save although no changes have been made yet. Expected Results: File to open without changes to be recognized Reproducible: Always User Profile Reset: No Additional Info: Just open and not have the save disk icon saying that changes need to be saved.
Created attachment 159363 [details] Bare minimum of the spreadsheet that reproduces the error This is the spreadsheet stripped down to the bare minimum that reproduces the error: Sheet "1": Cell A1 with the OFFSET formula Sheet "2": A chart. It now references to A1, since all other cells have been deleted. In the original file it references to other cells. If needed I can provide previous steps of stripping down the spreadsheet, I saved over 50 steps.
(In reply to tom from comment #1) > Created attachment 159363 [details] > Bare minimum of the spreadsheet that reproduces the error > > This is the spreadsheet stripped down to the bare minimum that reproduces > the error: > > Sheet "1": Cell A1 with the OFFSET formula > Sheet "2": A chart. It now references to A1, since all other cells have been > deleted. In the original file it references to other cells. > > If needed I can provide previous steps of stripping down the spreadsheet, I > saved over 50 steps. I can reproduce with this file. When I delete chart, then the file is not flagged as changed after opening.
Yes, anything I left over in the file is needed to reproduce the error. If either the cell with the formula or the chart are removed, the error disappears. Originally there were many more cells involved which referred to each other, and removing these just like that, made the error disappear, too. So I removed these cells one after the other by un-linking them with each other, until the point as it is in the end, only having the OFFSET cell and the chart. I could not reduce the spreadsheet further without losing the effect of the error.
Maybe because OFFSET() It's a volatile function like NOW() or INDIRECT(), so it is always recalculated with any change in the spreadsheet.
(In reply to m.a.riosv from comment #4) > Maybe because OFFSET() It's a volatile function like NOW() or INDIRECT(), so > it is always recalculated with any change in the spreadsheet. I could not reproduce the same effect when just using OFFSET in a different file, or in the same file when deleting the chart on the other sheet, which seems to be part of the problem.
Seems recreate the chart triggers OFFSET(), in many situations OFFSET() can be replaced with INDEX(), which it's not a volatile function. I'm not sure it is really a bug, or a situation to deal when using volatile functions on a chart's data range.
(In reply to m.a.riosv from comment #6) > Seems recreate the chart triggers OFFSET(), in many situations OFFSET() can > be replaced with INDEX(), which it's not a volatile function. > > I'm not sure it is really a bug, or a situation to deal when using volatile > functions on a chart's data range. @Eike, I thought you might be interested in this issue
OFFSET() is a volatile function recalculated (like RAND() and others) because no dependencies can be established between the calculated referenced cell(range) and the formula cell containing the OFFSET() expression. A chart referencing such expression directly or indirectly leads to recalculation because the chart asks for the current data of its range and hence the document is marked as changed. This *might* be solvable by assigning another volatile classification to OFFSET() (and INDIRECT()) so that they are not recalculated just after load upon first dereference, but may also need some document state assuring that so far no user or macro action whatsoever has occurred.
(In reply to Eike Rathke from comment #8) > OFFSET() is a volatile function recalculated (like RAND() and others) > ..... > so far no user or macro action whatsoever has occurred. +100 if they lose their volatile status. I try to avoid them because of this issue. And I don't recall any situation where I needed them to be volatile. E.g. INDIRECT() has a very valuable used to create links to other files, without modify formulas or editing links, but normally it does not need to be recalculated every time there is some kind of modification on the sheet, only when its precedents are modified. Or in many cases people use them massively, without knowing the problem of volatility. Maybe there are special cases where they need to be volatile, but I think it can be forced with simple tricks.
No simple trick there. Apart from maybe external references that are cached until updated (for which the dependency then would have to be individually remembered), as soon as and each time the reference string argument of INDIRECT() is changed or recalculated the old listening would had to be discarded and a new one based on the new argument be established. Similar for OFFSET(). For the initial load that would even be counter productive because the arguments would have to be calculated to determine whether or if and how the function would have to be recalculated later. Additionally, in array context it gets more complicated.
Dear tom, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug