Bug 35965 - UI: Result of =SHEETS() only updated using context menu "Insert Sheet"
Summary: UI: Result of =SHEETS() only updated using context menu "Insert Sheet"
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.3.2 release
Hardware: Other All
: medium normal
Assignee: Kohei Yoshida
Whiteboard: target:3.5
Depends on:
Reported: 2011-04-04 12:48 UTC by p_kongstad
Modified: 2011-12-23 15:46 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

sample document, see Comment 7 (9.58 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-09-14 02:27 UTC, Rainer Bielefeld Retired

Note You need to log in before you can comment on or make changes to this bug.
Description p_kongstad 2011-04-04 12:48:45 UTC
If you in a spreadsheet use the formula =sheets() and add a spreadsheet at the end the number is not being updated. F9 Recalculate does no change either.

If you insert a sheet in between other sheets it gets updated. The effect of deleting a sheet is also forcing to change the number.

To reproduce:

Open a new sheet (It has standard 3)

Make the formula =sheets() in sheet1. It will show you 3.

Press on the plus sign for adding another sheet.

The function still will show you 3 instead of 4. If you make the same formula in another cell it will show you 4.
Comment 1 p_kongstad 2011-04-04 12:57:22 UTC
I have just tested the same in OOo 3.4.0  Dev300M104 build 9579 and this is doing the same.
Comment 2 Jan Holesovsky 2011-04-07 11:55:52 UTC
Kohei: Is it a bug, or a feature? ;-)
Comment 3 Markus Mohrhard 2011-07-20 04:06:07 UTC
I think that we don't force a recalculation when we insert sheets and I don't think that we want to force it since this would dramatically decrease the performance
Comment 4 p_kongstad 2011-07-21 03:06:08 UTC
On 07/20/2011 01:06 PM, bugzilla-daemon@freedesktop.org wrote:
> https://bugs.freedesktop.org/show_bug.cgi?id=35965
> --- Comment #3 from markus.mohrhard@googlemail.com 2011-07-20 04:06:07
PDT ---
> I think that we don't force a recalculation when we insert sheets and I
> think that we want to force it since this would dramatically decrease the
> performance
Dear Markus,

But even a recalculation will not rectify the issue. So I still think
that this is a bug. Please test yourself.
Comment 5 p_kongstad 2011-07-23 01:18:01 UTC
The bug is present in 3.4.2RC2 as well.
Comment 6 p_kongstad 2011-07-26 09:36:26 UTC

I have found out that CTRL+SHIFT+F9 will update the value.
Comment 7 Rainer Bielefeld Retired 2011-09-14 02:24:04 UTC
[Reproducible] with "LibreOffice Portable 3.3.3  - WIN7  Home Premium (64bit) German UI [OOO330m19 (Build:301  Tag]"  ... Server installation of Master "LibO-dev 3.5.0 – WIN7 Home Premium (64bit) English UI [(Build ID:  d3d1481-3f8994a-2ba0a9f)]".

We can discuss various update concepts, but it's not understandable why the result will be updated when I insert a sheet using context menu on sheet-tabs, but not when I use "+ Sheet Tab" or menu 'Insert -> Sheet'

Currently I see this one as an UI inconsistence.

Please feel free to reassign (or reset Assignee to default) if it’s not your area or if provided information is not sufficient. Please set Status to ASSIGNED if you accept this Bug.
Comment 8 Rainer Bielefeld Retired 2011-09-14 02:27:18 UTC
Created attachment 51185 [details]
sample document, see Comment 7

BTW, OOo 3.3 updates result after menu 'Insert -> Sheet'
Comment 9 Kohei Yoshida 2011-09-14 08:20:43 UTC
When you do

1. Create a new document.
2. Type in Sheet1.A1 =Sheets()
3. Click on Sheet3.
4. Insert -> Sheets and insert a new sheet *after* current sheet.
5. Go back to Sheet1.

The value in A1 is still 3.

However, when you change step 4 to insert a new sheet *before* current sheet, the value in A1 becomes 4.

So, this has nothing to do with the plus button but has everything to do with a bug in the insert table code not marking cells dirty when appending a new sheet to the end of the last one.

When you take a look at code in ScDocument::InsertTab(), it calls SetDirty() only when nPos < nTabCount, which is evaluated to false when adding a new sheet to the end position.  That's what's causing this bug.
Comment 10 Kohei Yoshida 2011-09-14 08:28:09 UTC
And yes, this may have a performance implication since inserting sheets causes a document-wide full re-calculation, but whether we like it or not this is what we currently do.

Fixing that performance issue (when that becomes an issue) would be outside the scope of this bug.
Comment 11 Kohei Yoshida 2011-09-14 08:36:52 UTC
Taking it.