Description: Using F4 for absolute cell referencing in Calc generally seems to work. However, if you cross refer from one sheet to another and the formula cell is in the main area of a sheet where rows & columns have been frozen then F4 seems to have no effect. To be clear, F4 only fails to work at the time of entering a formula by clicking on target cells. If you hit enter and go back to edit the formula, then F4 will work. Steps to Reproduce: 1. Create a new a new Calc spreadsheet. 2. Add a second sheet to the spreadsheet. 3. In Sheet 1, click on cell B2, then View → Freeze Rows and Columns 4. Enter a formula in Sheet 1, Cell B2 (by clicking on the target cells), for example, create “=Sheet2.A1”, by clicking typing "=" and then clicking on cell A1 in Sheet2. 5. if you then hit F4 (before hitting enter), the absolute reference does not work. 5. However if you press return to enter the formula, then you can then click on Sheet 1, Cell B2 again, and F4 will now work. Actual Results: F4 fails to work at the time of creating the formula by clicking on the target cells in another sheet. If you hit enter, then you can go back into the formula and F4 will work. When you are constructing complex expressions with multiple terms, having to go back to edit them (rather than being able to create absolute references as you go along) is not a very satisfactory solution. Expected Results: F4 should toggle absolute cell referencing on each occasion that it is used. Reproducible: Always User Profile Reset: No Additional Info: F4 works if you type in the reference manually. But if you create the contents of the formula cell by clicking on the the target cell(s) (as you normally do when constructing a spreadsheet), then F4 doesn’t work. F4 also works if freeze rows and columns not enabled or if reference cells are within the same sheet.
Created attachment 185317 [details] Example Calc spreadsheet (to illustrate bug)
Confirm in Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 4e6ab75c1a907398d24768d19cf097a4892d374c CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: x11 Locale: cs-CZ (cs_CZ.UTF-8); UI: en-US Calc: threaded Works in Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)
This seems to have begun at the below commit. Adding Cc: to Justin Luth ; Could you possibly take a look at this one? Thanks 980f7a1f4a63342f1388a4216a802eea6ae4cc56 is the first bad commit commit 980f7a1f4a63342f1388a4216a802eea6ae4cc56 Author: Jenkins Build User <tdf@pollux.tdf> Date: Tue Nov 17 21:04:51 2020 +0100 source fed6c4c70da6b35d72b670c8f4d8e866cdac21e4 https://git.libreoffice.org/core/+/fed6c4c70da6b35d72b670c8f4d8e866cdac21e4
Ignoring the bibisect result - is OP reporting something that he noted that worked in the past, but no longer works anymore? I don't think so. I tested in LO 7.0, and if I start from sheet 2, and try to build an equation from sheet 1, then F4 never works. So I think the general problem has pre-dated my patch. In fact, following these procedures, prior to my patch there WASN'T any ability to build a formula. 1.) Open F4 Test.ods in LO 7.0 and place the cursor in Sheet 1.B2 2.) Press "=" to start a new formula, click on Sheet 2 cell A1 -observe - nothing happens in 7.0, still just an equal sign, so nothing to use F4 on. 3.) Cancel the current build. place cursor in D2 4.) Press "=" to start a new formula, click on Sheet 2 cell A1 -observe - "=$Sheet2.A1", but pressing F4 does nothing. After my patch(7.1), doing steps 1 and 2 now looks like the result from 3 and 4. -observe - "=$Sheet2.A1", but pressing F4 does nothing. The ability to press F4 started after 4.4 and before 5.3 - and I don't have the ability to bibisect in that range. Almost certainly it has never worked.
I agree it is hard to tell, as the formula was not visible before Justin's commit. However, if you add the UNO command uno:ToggleRelative to one of the menus or the toolbars, you can see it greyed out after following Nigel's steps. Instead of a regression, should this be called an implementation error? We can't say for sure that it started as early as 5.3 because the command is not greyed out following the same steps prior to 7.1.
(In reply to Justin L from comment #4) > The ability to press F4 started after 4.4 and before 5.3 - and I don't have > the ability to bibisect in that range. Almost certainly it has never worked. Just a reminder: changing / cycling between relative and absolute references was performed with [SHIFT]+[F4] until LO 5.0.x. It was modified from that shortcut to [F4] for LO 5.1. See Release Notes. Whether it worked or not in the past, it should always be available when in edit mode. When building a formula the first time or modifying one, by typing in, or by Formula Wizard, or by clicking worksheets and cells, or by... Having the possibility of cycling between absolute and relative references should work in every case that edit mode is active, whichever the method in use, and independently of having freeze or split areas. OTOH, there was some opposition back then. See bug 59418.
BTW, when following the steps (i.e. with freeze area), with the adjustment to use [SHIFT]+[F4] I can confirm that it worked in LO 3.3, whereas with [F4] in LO 7.4.5, it fails.
(In reply to ady from comment #7) > use [SHIFT]+[F4] for older versions of LO. This is a great piece of information. Thanks. Please use the starting cell of Sheet1.D2. We already know that because of a bug the two cells adjacent to a split confuse the testing of this bug. (In reply to ady from comment #6) > Whether it worked or not in the past, it should always be available... Agreed. However, finding out if it worked before is critical to the development process. If it worked before, we can look for the commit that broke it. If it never worked, then it becomes a lot more complicated to fix. I see (using shift-F4 and D1) that this didn't work in the oldest LO I can check - 3.6. I highly suspect that it is inherited from OOo. P.S. I also see the same problem when starting from a non-frozen sheet (i.e. Sheet 2) with the destination being a frozen sheet.
(In reply to Justin L from comment #8) > (In reply to ady from comment #7) > > use [SHIFT]+[F4] for older versions of LO. > This is a great piece of information. Thanks. > > Please use the starting cell of Sheet1.D2. We already know that because of a > bug the two cells adjacent to a split confuse the testing of this bug. > > (In reply to ady from comment #6) > > Whether it worked or not in the past, it should always be available... > Agreed. However, finding out if it worked before is critical to the > development process. If it worked before, we can look for the commit that > broke it. If it never worked, then it becomes a lot more complicated to fix. > > I see (using shift-F4 and D1) that this didn't work in the oldest LO I can > check - 3.6. I highly suspect that it is inherited from OOo. > > P.S. I also see the same problem when starting from a non-frozen sheet (i.e. > Sheet 2) with the destination being a frozen sheet. Thank you both. Confirmed that it is inherited. In OOo 3.3, freezing is in Window > Freeze, and the UNO command can be found in the Customize dialog in the "Insert" category, "Relative/Absolute References" command. It is greyed out when following the steps in Description (using cell D1 for the formula).
(In reply to Stéphane Guillou (stragu) from comment #9) > Thank you both. Confirmed that it is inherited. > > In OOo 3.3, freezing is in Window > Freeze, and the UNO command can be found > in the Customize dialog in the "Insert" category, "Relative/Absolute > References" command. It is greyed out when following the steps in > Description (using cell D1 for the formula). Well, it partially works with the old [SHIFT]+[F4] in LO 3.3. If I freeze, and in the same cell I click the "=" icon, select the immediately-adjacent sheet and click on A1, [SHIFT]+[F4], I see it works. I can press [ENTER] and the formula sticks. But, if I change to a different cell (i.e. not the same cell where the freeze was introduced), then the procedure fails. I can also successfully do it if the sheet is not the immediately-adjacent, or the cell in that sheet is not A1. But all this depends on starting at the new "home" cell in the freeze area. Another important detail: in old versions, the sheet reference was also initially relative, same as the cell reference. In current versions, the sheet reference starts as absolute already. This part is not a bug; this was an intentional change! Please keep this part as it is now!
(In reply to Justin L from comment #8) > We already know that because of a > bug the two cells adjacent to a split confuse the testing of this bug (In reply to ady from comment #10) > Well, it partially works with the old [SHIFT]+[F4] in LO 3.3. If I freeze, > and in the same cell I click the "=" icon, select the immediately-adjacent > sheet and click on A1, [SHIFT]+[F4], I see it works. Whether it works or not in that specific situation is basically irrelevant. I tried to discourage testing that situation with comment 8. Having a uno name is a great code pointer. Thanks for providing that. The name of the toolbar control to search for is "Cycle Cell Reference Types". As soon as I switch to a sheet with a frozen column (not row) then this toolbar disables (even before selecting any cell). [For fun, add a third sheet. Starting from sheet 2, start a formula and select a cell in sheet 3. F4 works. Now switch to sheet 1. Although it still references a sheet3 cell, F4 no longer functions. So the disabling of the command is the code that needs to be examined. In the code, grepping for SID_TOGGLE_REL leads to sc/source/ui/view/editsh.cxx's Execute() function. This function is not even called when F4 is pressed in a frozen sheet. I can "fix" the issue by removing "StateMethod = GetState;" from editsh.sdi.
Also fixable by setting FastCall = TRUE. (icon still disabled, by F4 key works) But I think the real fix is to not lcl_DisableAll states when IsFormulaMode.
Justin Luth committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/57835f3e2524060fcb021ea5dfb31f81c089d4f1 tdf#153548 sc: don't disable all state when IsFormulaMode It will be available in 7.6.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Commit Notification from comment #13) > Affected users are encouraged to test the fix and report feedback. I tested with today's 7.6.alpha. I need to warn about a changed behavior here, because it used to bring a lot of problems in the past. As I wrote in comment 10 (quote): Another important detail: in old versions, the sheet reference was also initially relative, same as the cell reference. In current versions, the sheet reference starts as absolute already. This part is not a bug; this was an intentional change! Please keep this part as it is now! After commit 57835f3e2524060fcb021ea5dfb31f81c089d4f1 , while the initial worksheet reference is still absolute, as soon as I pressed F4 it turned to relative. In order to make it absolute again, we need to press F4 several times (even going back to full relative before starting with absolute worksheet reference). This would bring the confusion from unaware users back again. There used to be many reports from users claiming that the formulas were failing after copying a worksheet. These finally stopped since the default reference for worksheet was set to absolute. This should still be the case. Instead of starting by absolute worksheet reference (before the first F4) and then going back to relative worksheet references for the first few F4, the behavior should still be to keep the absolute references for worksheets while cycling the "$" around the cell address; and only _then_ change the worksheet reference to relative (and continue with the rest of the cycle). IOW, the current behavior regarding the worksheet references (first priority being absolute) should not change.
The reference cycling from absolute sheet reference to relative if both column and row are relative and those becoming absolute (e.g. $Sheet2.A2 -> Sheet2.$A$2) is standard behaviour of the reference cycler though that doesn't know anything about what created the reference first hand, and was always the case unrelated to this bug here. This bug here is fixed. If change in behaviour of the reference cycler is wanted then please open a new RFE bug.
(In reply to Eike Rathke from comment #15) > The reference cycling from absolute sheet reference to relative if both > column and row are relative and those becoming absolute (e.g. $Sheet2.A2 -> > Sheet2.$A$2) is standard behaviour of the reference cycler though that > doesn't know anything about what created the reference first hand, and was > always the case unrelated to this bug here. This bug here is fixed. If > change in behaviour of the reference cycler is wanted then please open a new > RFE bug. That is not my experience. Although, we might have a different behavior when initially creating a formula vs. editing, or when typing vs. clicking, at least in part of the cases the cycle starts with absolute reference for worksheet and users have to press F4 several times to get to the relative reference for worksheet. For users that are already aware of the feature (i.e. abs/rel references for worksheets), this should not be a big problem. It might get them to some unexpected error, but they should be able to eventually spot it. For users that are not aware of the feature (i.e. abs/rel references for worksheets), I can only hope that this doesn't start the reports again as it used to be back then, because it is not always clear that the problem is lack of awareness of the abs/rel references for worksheets.
(In reply to Eike Rathke from comment #15) > If change in behaviour of the reference cycler is wanted > then please open a new bug. Done. It is enhancement bug 154075.