Ah! My response was based on entering the whole formula through the keyboard. That's my modus operandi (sp?).
You're talking about using the mouse to generate references. I'll back away as I have nothing useful to say about your method. -- Jim -----Original Message----- From: Ady <[email protected]> To: [email protected] Sent: Wed, 22 Apr 2015 14:09 Subject: Re: [libreoffice-users] Re: [Calc] Relative references behavior for worksheets > Replicating your steps, I get a copy of Sheet2 at position #1 and any > relative reference to the previous sheet needs to raise a #REF! error > because you told me to reference a cell on the previous sheet. The error is > perfectly clear, wanted and informative. There is nothing wrong with errors. > Errors are not a slap in your face. They are not errors because you are > stupid. I can not see any error in the application. Everything works as > expected. If you reference something something irreferenciable you get a > #REF! error. > I seem to have a problem explaining the matter. I do understand how it works. I understand the error. I understand why it is giving this error. I am not complaining about receiving an error. I'll try to explain my point in a different way. Let's assume the following procedure: 1_ Click on A2. 2_ Type in an equal sign, "=". 3_ Click on A1. 4_ Press [Enter] 5_ Click on A2. The resulting formula is: A2: =A1 The default behavior, as seen in this simple procedure, is that cells are referenced with relative notation. If the resulting formula would had been (by default and with no additional steps/help): A2: =$A$1 we would had concluded that the default behavior was absolute references. But we know this is not the default behavior, and users take advantage of this. All spreadsheet programs that I know of behave in this same way. To receive a formula with absolute references in Calc we would need some extra step(s) (e.g. [Shift-F4]). Now let's repeat the procedure, with a slight difference: 1_ Click on Sheet2.A2. 2_ Type in an equal sign, "=". 3_ Click on Sheet1. 4_ Click on Sheet1.A1 5_ Press [Enter] 6_ Click on Sheet2. 7_ Click on Sheet2.A2. The resulting formula is: Sheet2.A2: =Sheet1.A1 Here, once again, the cell references are, by default, relative. Since we now involved multiple sheets in the formula, the resulting formula includes the sheet(s) as part of the reference. And we also see that, by default, the reference to "Sheet1" is also a relative one. Here is where Calc goes differently than other spreadsheet programs, and it is here where users (that are used to other spreadsheet programs) are having problems (and even reporting this behavior as a bug, multiple times already, since they don't understand why it is failing, considering that they are used to a different default behavior). When using other spreadsheet programs, the resulting formula for the last procedure would had been: "absolute reference for 'Sheet1' and relative reference for its 'A1' cell". Or, using Calc's notation: Sheet2.A2: =$Sheet1.A1 (note the "$"). What I am trying to convey is that Calc should change the default behavior for referencing sheets, so to behave as other spreadsheet programs do. I am not saying that: Sheet2.A2: =Sheet1.A1 is wrong, or that I don't understand the "#REF!" error, or that I don't understand why the error is being generated after the copy+paste procedure I described in my initial email. I am saying that the _default_ behavior should be to obtain absolute references to sheets (while keeping relative references to their cells). I am not suggesting to change the meaning of the "$" in front of the sheet. I am not suggesting to change the behavior of the "REF!" error nor its meaning. I _am_ suggesting that, by default, the sheets in Calc should be using the "$" in front of them. If a user wants to use relative notation for sheets, then such result should had been obtained by adding some step (e.g. explicitly deleting the "$" in front of the sheet reference), instead of obtaining a relative reference to the sheet by default, as it is now. By changing the default behavior regarding default references to sheets, Calc would be simply imitating what other worksheet programs already do, and less "REF!" situation would be encountered by users. Additionally, it is easier to replace absolute references to sheets with relative references to sheets, whereas it can be very difficult to find and correct every "REF!" in complex workbooks. It is the *default* behavior of "references to sheets" that I am talking about. Hopefully I am making it more clear now. Now, if my experience with other spreadsheet programs (as I described it here) is different than other users here in the list, I would like to know about it. If the tests / steps I have presented in this email thread cannot be replicated by others, or if the default behavior seen by others is different than what I am seeing, I would really appreciate receiving feedback about it, because it would mean that I could change the default behavior in my own setup, without waiting for developers to do anything. Thank you in advance, Ady. -- To unsubscribe e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
