There was a complaint in bug 153548 that when formula-building and selecting a cell from another sheet that "Cycle Cell Reference Types" would be better if it first switched to full absolute, then none, then partial. The initial state is a partial absolute $Sheet.A1 - which is a good initial state. The code that handles the cycle is sc/source/core/tool/reffind.cxx's lcl_NextFlags The order that the toggle follows is sc/inc/address.hxx TAB_ABS = 0x0004 ROW_ABS = 0x0002, COL_ABS = 0x0001, ZERO = 0x0000, The formula decreases by one from the current position, and from 0 to 7. The starting point this report is concerned with is 4. To see this in action: -open attachment 185317 [details] (F4 Test.ods), press "=" and select A1 in Sheet 2 -notice the initial formula is "=$Sheet2.A1" -press F4 and cycle through the 8 absolute choices I think the suggestion then is to reverse the order of the cycle. That way when you start with the sheet as an absolute, you start adding cell and row until fully absolute before making the whole thing relative. I'm sure this change would raise the ire of all those who like to press F4 once to make the most common task of changing from relative to fully absolute instead of taking 7 steps to do that task. Perhaps the place of COL and TAB could be switched around, but that would cause the rather unpleasant situation of alternating an absolute sheet back and forth. Or is there some other kind of magic equation that could do the following order: 0 - none 7 - full 1 - col 2 - row 3 - cell 6 - sheet + row 5 - sheet + col 4 - sheet The other option would be to have a "remember the last used toggle" state and combine a "toggle session" with the "current toggle state" and have some special action for the "first toggle in a session" - which is probably the only acceptable solution for this request.
As of: Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 0484a9a3f5e2ecb678f6fb41bbb251529e89c00d CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: en-US (es_AR); UI: en-US Calc: CL threaded Built 2023.03.07 Currently the cycle goes like this: 0. $Sheet2.A1 < this is before pressing F4. With successive F4, the cycle currently continues as: 1. Sheet2.$A$1 2. Sheet2.A$1 3. Sheet2.$A1 4. Sheet2.A1 5. $Sheet2.$A$1 6. $Sheet2.A$1 7. $Sheet2.$A1 8. $Sheet2.A1 > we are back to the same status as step 0 above. The suggestion is for the F4 cycle to work as follows: 0. $Sheet2.A1 < this is before pressing F4 (no change) 1. $Sheet2.$A$1 2. $Sheet2.A$1 3. $Sheet2.$A1 4. Sheet2.A1 5. Sheet2.$A$1 6. Sheet2.A$1 7. Sheet2.$A1 8. $Sheet2.A1 > we are back to the same as step 0 above. So, the suggested cycle changes nothing regarding rows and columns. FWIW and FYI, a little bit of background history... Absolute and relative references for cells/rows/columns are supported in every spreadsheet software, but relative references for worksheets is not as widely supported, and thus it is a less-known feature. Users that are unaware of the existence of relative references for worksheets used to complain that some formulas didn't work when copying entire worksheets several times (e.g. more than 2 worksheets with the first one as "template" or as “summarize”, or one plus a dozen worksheets that were not working all the same as they expected) and so they used to report the behavior as a bug; that is, without anyone knowing that the real problem was that the worksheets' references were relative, instead of the "expected" absolute reference, which was the only type they knew. The repetitive reports, forum posts, investigations and explanations were all reduced when the default worksheet reference was set as absolute instead of the prior relative reference. This was a few years back already. For most users, the suggested modified cycle should not have negative consequences, and the amount of F4s might probably be reduced rather than increased.
(In reply to ady from comment #1) > The suggestion is for the F4 cycle to work as follows: > 0. $Sheet2.A1 < this is before pressing F4 (no change) > > 1. $Sheet2.$A$1 > 2. $Sheet2.A$1 > 3. $Sheet2.$A1 > 4. Sheet2.A1 > 5. Sheet2.$A$1 > 6. Sheet2.A$1 > 7. Sheet2.$A1 > 8. $Sheet2.A1 > we are back to the same as step 0 above. > > So, the suggested cycle changes nothing regarding rows and columns. BTW, the suggested cycle is also already the current cycle when editing a formula that was already introduced, for example by the Formula Wizard. PS: having F4 also available when using the Formula Wizard would another RFE.
(In reply to ady from comment #2) > BTW, the suggested cycle is also already the current cycle when editing a > formula that was already introduced, for example by the Formula Wizard. Sorry, I think I was not clear enough. What I mean is that when having, for instance, $Sheet2.$A$1 already in the formula bar as part of a formula that was previously introduced, then getting into edit mode again and clicking on the reference, the F4 cycle continues in the same way as suggested. The point is that in such cases, there is also no disruption; the cycle continues in the same way as it is now. I hope I was clearer now.
(In reply to ady from comment #3) I cannot reproduce your sequence. If I just type in the formula area "=$Sheet1.$A$1" and then cycle with F4 I get: -$Sheet1.B$2 = sheet + row (aka 6) -$Sheet1.$B2 = sheet + col (aka 5) -$Sheet1.B2 = sheet (aka 4) - you said this would become Sheet.B2 (aka 0) -Sheet1.$B$2 = cell (aka 3) -Sheet1.B$2 = row (aka 2) -Sheet1.$B2 = col (aka 1) -Sheet1.B2 = relative (aka 0) - you said this would become $Sheet.B2 (aka 4) I'm pretty sure you are mistaken because the code path I found is a very simple "subtract by 1" formula. If you are not mistaken, please provide the exact formula/steps that you are following to trigger this cycle.
(In reply to Justin L from comment #4) > please provide the > exact formula/steps that you are following to trigger this cycle. Using: Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 0484a9a3f5e2ecb678f6fb41bbb251529e89c00d CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: en-US (es_AR); UI: en-US Calc: CL threaded Built 2023.03.07 1. Open new Calc with Sheet1 and Sheet2 2. With the focus on Sheet1.A1, click on the "=" icon on the Formula bar. 3. Click on Sheet2's tab. 4. Click on Sheet2.A1 (do _not_ press [enter]) 5. Currently the Formula bar shows $Sheet2.A1 (step 0), still in edit mode (the formula is not yet fully introduced). By pressing F4 we cycle: Sheet2.$A$1 Sheet2.A$1 Sheet2.$A1 Sheet2.A1 $Sheet2.$A$1 $Sheet2.A$1 $Sheet2.$A1 $Sheet2.A1 > we are back to the same status as step 0 above. At any given step, we can press [enter]. Evidently, when users type-in the formula, there is no "default" type of reference, other than whatever they typed-in. From that point, the cycle should be in the same order as I described, depending on what they initially typed-in. The concept of "default" references are relevant for 2 alternative situations: * when clicking on the cells (while building the formula; that is, the function is typed-in, and the arguments are selected by mouse+click); or, * when using the Formula wizard, in which case the "default" type of references varies depending on the function and the argument. Unfortunately, while using the Formula Wizard (whether in full mode or shrink mode), the F4 key doesn't work (I wished it would, because it saves a lot of time when it works in it, in other spreadsheet programs). So I am mentioning here the first alternative, as I just described. Hopefully this clarifies the steps / procedure. From it, I am suggesting to change the behavior as I posted in comment 1.
(In reply to ady from comment #3) > I cannot reproduce your sequence. Sorry - I mis-read. I thought you said there was an existing case where your SUGGESTION was followed. Although I have no intention of proposing that this be accepted, I have coded up your suggested sequence. https://gerrit.libreoffice.org/c/core/+/148516
(In reply to Justin L from comment #6) > I have > coded up your suggested sequence. > https://gerrit.libreoffice.org/c/core/+/148516 I have no coding skills/knowledge in order to comment about it. I do thank you. To sum-up, from the POV of final users, the resulting change should be: 0. $Sheet2.A1 0. $Sheet2.A1 1. Sheet2.$A$1 1. $Sheet2.$A$1 2. Sheet2.A$1 2. $Sheet2.A$1 3. Sheet2.$A1 3. $Sheet2.$A1 4. Sheet2.A1 4. Sheet2.A1 5. $Sheet2.$A$1 5. Sheet2.$A$1 6. $Sheet2.A$1 6. Sheet2.A$1 7. $Sheet2.$A1 7. Sheet2.$A1 It is clear that the proposed change is in the absolute vs relative reference for worksheets, and there is no effect for rows/columns. For same-sheet formulas, there is no effect either. I think it would reduce some confusion (and unneeded "bug reports" and forum posts) on users that are unaware of the existence of relative worksheet references, and it would also reduce the amount of F4s.
> To sum-up, from the POV of final users, the resulting change should be: > From current: To Proposed: > 0. $Sheet2.A1 0. $Sheet2.A1 > 1. Sheet2.$A$1 1. $Sheet2.$A$1 > 2. Sheet2.A$1 2. $Sheet2.A$1 > 3. Sheet2.$A1 3. $Sheet2.$A1 > 4. Sheet2.A1 4. Sheet2.A1 > 5. $Sheet2.$A$1 5. Sheet2.$A$1 > 6. $Sheet2.A$1 6. Sheet2.A$1 > 7. $Sheet2.$A1 7. Sheet2.$A1 > > It is clear that the proposed change is in the absolute vs relative > reference for worksheets, and there is no effect for rows/columns. For > same-sheet formulas, there is no effect either.
Hard to imagine that user toggles trough all eight states to find the right one. And what I miss in all the discussion is the opposite sequence starting from relative references. The primary use case is likely to switch from (any) relative to absolute references ?Sheet?2.?A?1 => $Sheet2.$A$1 and the opposite ?Sheet?2.?A?1 => Sheet2.A1, where we obviously know the relative state only if there is zero absolute tag. Sheet2.A1 => $Sheet2.$A$1, ... ?Sheet2.?A?1 => Sheet2.A1, $Sheet2.$A$1, ... The sequence after full absolute is probably not so important and the proposal is okay. (In reply to Justin L from comment #0) > The other option would be to have a "remember the last used toggle" state > and combine a "toggle session" with the "current toggle state" and have some > special action for the "first toggle in a session" - which is probably the > only acceptable solution for this request. Basically I like this idea but the on/off scenario seems to be typical meaning you don't have one single last used toggle state. Perhaps as second/third option the one that was picked last from the many options?
(In reply to Heiko Tietze from comment #9) > Hard to imagine that user toggles trough all eight states to find the right > one. And what I miss in all the discussion is the opposite sequence starting > from relative references. In the current cycle, when I need to use F4s and the reference includes a worksheet name, I end up using between five (5) and seven (7) F4s, because it is very rare for me to use relative _worksheet_ references. I repeat that the changes for Row and Column are non-existent; i.e. there is no effect on them because the proposed cycle keeps them in the same order. No user needs to use eight F4s on the same reference, unless the user realizes he made some mistake so he goes through another cycle (or part of it) in order to arrive to the desired reference type (again). The current initial state (i.e. "0" above) is correct, not only because it is the most common when worksheet references are included (in most other spreadsheet software _too_) but also because, when it was implemented, it reduced the amount of complaints and reports about "something" in Calc not working. The proposed improvement here would start from the first F4 onwards. @Heiko, sincerely, other than what I just mentioned, I didn't understand any of those questions (perhaps I need some resting? Or perhaps they are not aimed at a common user?). Either I am a very particular user (I don't think so), or we have very different experiences actually using spreadsheet software for real formula / spreadsheet building.
(In reply to ady from comment #10) > ...it is very rare for me to use relative _worksheet_ references. That's true. My proposed first toggle on/off should ignore what is set for the sheet. > No user needs to use eight F4s on the same reference... If the sequence is always the same with ABCDE, you may start at A going to B and vice versa from B to A. One is easy to achieve the other not so. If you always start from A even when the actual state is B, this is less of a problem, of course. Changing from C to D would be a challenge then, however. My point is that you cannot click through eight options in a reasonable time. We may a) reduce the number of options or b) make it easier to access by changing the sort order according the probability of needs. I have some doubts in these "smart" functions, on the other hand. It takes a lot of assumptions and might be annoying for users. I can understand if you reject my comments. Justin's idea is an improvement by itself.
(In reply to Heiko Tietze from comment #11) > (In reply to ady from comment #10) > > ...it is very rare for me to use relative _worksheet_ references. > > That's true. My proposed first toggle on/off should ignore what is set for > the sheet. When the reference has no worksheet name, that is how it works. When the reference includes a worksheet name, the worksheet name is also considered within the F4 cycle. Are you proposing that the F4 cycle should modify the Row and Column types only, while leaving the worksheet name to be modified "manually" (i.e. F4 would not act on the reference type of the worksheet)? FWIW, I would vote against, if anyone asks. > > > No user needs to use eight F4s on the same reference... > > If the sequence is always the same with ABCDE, you may start at A going to B > and vice versa from B to A. One is easy to achieve the other not so. If you > always start from A even when the actual state is B, this is less of a > problem, of course. Changing from C to D would be a challenge then, however. All my proposal was/is doing is changing the order of worksheet references' types as seen in comment 8. I am not proposing other changes in behavior, which you seem to be proposing(?). In the current F4s cycle, if the formula is (already saved, we are no longer in edit mode): =$Sheet2.A$1 ...which currently corresponds to what I called "step 6", and then I get into edit mode (press F2 while the cell with the formula is in focus) and then press F4 once, the formula changes to: =$Sheet2.$A1 ...which corresponds to the current "step 7". Even when I am pressing F4 just once since starting edit mode, it doesn't mean I'm going back to "step 1" with just that one F4. In my proposal, this is all exactly the same, except I called them "step 2" (instead of the current "step 6") and "step 3" (current "step 7") respectively. If the formula were to include several references, each one has its own "step" and they can be changed by positioning the cursor on any part of a reference or by selecting it, while the other references in the same formula are not modified by pressing F4. This is how it currently works, and my proposal changes nothing about it. This is all being described from the POV of users and how the behavior looks (and will still look). If the source code to keep this same behavior is more complicated than it is now, that is beyond my skills and knowledge. > > My point is that you cannot click through eight options in a reasonable > time. I don't know what that means. What reasonable time are we talking about? What clicks? I'm sorry, but I don't understand. Should I link to some youtube videos showing how this works for experienced Excel users, where the F4 method is _much_ more frequently used? LO Calc takes advantage of F4s in only a small part of the UX situations in comparison to Excel, even when Calc has relative references for worksheet and Excel doesn't. The whole point of using F4 is that it reduces both, clicks and typing "$" in specific positions. In Excel it has additional advantages too. > time. We may a) reduce the number of options or b) make it easier to access > by changing the sort order according the probability of needs. The cycle must be the always same (as per memory muscle). The proposal is limited, as already described. The "first" F4 moves the reference type to the "next" step. Starting from step 5, the first F4 modifies the reference to step 6, and so on. Starting from step 0 (zero) is just the "default" case when no edition was ever performed to the reference type. Moreover, the "default" type is not the same when using the Formula Wizard, but when later-on we use F4 (because sadly Calc's FW doesn't accept F4s), the F4s cycle is the same as described. It doesn't "start over" from step 0 (zero). No probabilities. No randomness, no assumptions, no different behavior depending on context. There is no starting over. The cycle is always the same: from "step 'n'" to "step 'n+1'". With eight F4s, we are back to whichever the starting step was before pressing the first F4. > > I have some doubts in these "smart" functions, on the other hand. It takes a > lot of assumptions and might be annoying for users. I can understand if you > reject my comments. Justin's idea is an improvement by itself. The reason for me to express the proposal from the POV of a common user is because Justin's expressions are as a developer, and I cannot be sure I interpret them as they were intended. Considering the amount of phrases I didn't understand already, I don't want to opine on any other proposal regarding F4 cycles unless I completely understand its consequences. I'm truly sorry I had to make this post so lengthy, but ATM I have the impression we are talking about different things and I needed to be explicitly clear about my proposal. If Justin presented a different idea than mine, then indeed I/we have a communication problem. Justin initiated this ticket as a response to my suggestion, which is summed-up in comment 8 here. If we are/were discussing other proposals, then I have no idea of what that is/was supposed to be.