Created attachment 67183 [details] Test screenshot. I had reported a bug (https://bugs.freedesktop.org/show_bug.cgi?id=54774) closed RESOLVED - NOTABUG, against my criteria without third opinion. Attached screenshot, with a small sample in a file from scratch. The Conditional Formatting formula for A1 is "ISTEXT(A1)", In A1 CF apply an style with green background, what is right, because A1 have text. Manage window show in First Condition column: "ISTEXT(B2)" B2 is the actual cell, not the address in CF formula for A1. Enhance: - First condition column must show the applied condition. Or if the manage window can not show the actual contents of the condition, IMHO better delete the First Condition column.
As I tried to explain, this is a feature and not a bug. Using relative references in conditional formats is one of the trickiest features we have. The formula is not ISTEXT(A1). The formula is actually more something like ISTEXT(current cell) or ISTEXT(cell one to the left) so showing ISTEXT(A1) would be totally wrong. Using relative references in conditional formats requires to abstract a bit and understand that a relative reference is not a reference to a specific cell, instead it only says something about how to find the cell based from the current cell. This does not only happen in the manage conditions dialog you'll also see the same in the normal cond format dialog because the formula is not an absolute formula, the formula is always a formula relative to the currently selected cell in the dialog and when evaluated it is against the current cell but always based on the original base cell. See the difference if you select A1:A4 and define a range ISTEXT(B5) when the currently selected cell is A1 or A4. You'll notice that this results in two different formulas but they will both show the formula string.
Attached the file source of previous screenshot. I it works as you said, then when I change to a blank cell, i.e. D5, the condition for A1 should be change from ISTEXT(A1) to ISTEXT(D5) and how D5 is blank, the condition is false, then A1 should be without format, but A1 remains with the condition true because there is a text in A1. So no care where I go, CF for A1 is calculate by their formula ISTEXT(A1), the condition is true, the select style is Green. I think I understand how the relative cells works, but in CF relative is different from absolute only when they are used to define the CF for a range. And only while evaluating a range, the CF formula should be evaluated like the arrays in the cells. And I think is how do it. After select A6:B8 enter in the CF ISTEXT(A6), then evaluation should be: A6 with ISTEXT(A6), B6 with ISTEXT(B6) A7 with ISTEXT(A7), B7 with ISTEXT(B7) A8 with ISTEXT(A8), B8 with ISTEXT(B8) After select A6:B8 enter in the CF ISTEXT($A6), then evaluation should be: A6 with ISTEXT($A6), B6 with ISTEXT($A6) A7 with ISTEXT($A7), B7 with ISTEXT($A7) A8 with ISTEXT($A8), B8 with ISTEXT($A8) After select A6:B8 I enter the CF ISTEXT(A$6), then evaluation must be: A6 with ISTEXT(A$6), B6 with ISTEXT(B$6) A7 with ISTEXT(A$6), B7 with ISTEXT(B$6) A8 with ISTEXT(A$6), B8 with ISTEXT(B$6) I need to insist, to me the calculations are doing properly, when you go trough Menu/Format/CondFormatting/CondFormatting/ you can see right the formula for the actual cell, differen if you go trough Menu/Format/CondFormatting/Manage/
Created attachment 67191 [details] File test
(In reply to comment #3) > Created attachment 67191 [details] > File test No this is not a bug. The formula is always calculated against the current cursor position. So if you open the Manage Conditional Format window when the cursor is in A3 you see both formulas with an A3 if you open it while it is in A1 you see it with A1. When you entered the formulas you actually told Calc that the formula is: ISTEXT(current cell) and not ISTEXT(always cell A1). The cseocnd one would be ISTEXT($A$1). As long as you're using conditional formats you have to understand that the relative part is always only relative to the cursor/currently evaluated cell.
Sorry Markus it was created as "enhancement", and I have no interest in maintaining a discussion with you to nowhere. So if you I am wrong or you don't like to do, no matter, but please return their status, to allow someone else take a look. Sorry again but to me what you explain is not how it works. In A1 cell, goto to CF introduce in the condition B1, select an style to apply. Enter 1 in B1, the CF condition is always true, and do not depend on the actual cell where we are, only on the B1 value, we can go around on the spreadsheet but A1-CF only change if we change the value in B1. Even worse, if it was not possible to use so the relative formulas in CF, then would be impossible in many cases copy CF between cells, because always remains with the formula of source cell(s). *Actually Copy doesn't copy the CF. Regards.
(In reply to comment #5) > Sorry Markus it was created as "enhancement", and I have no interest in > maintaining a discussion with you to nowhere. So if you I am wrong or you don't > like to do, no matter, but please return their status, to allow someone else > take a look. Sorry, but this is not a bug. This bug should not be reopened and there will be nobody else looking at this bug report. If you still feel that you're right and know calc better than me write to the qa list and complain about me closing this bug. > > Sorry again but to me what you explain is not how it works. > > In A1 cell, goto to CF introduce in the condition B1, select an style to apply. > Enter 1 in B1, the CF condition is always true, and do not depend on the actual > cell where we are, only on the B1 value, we can go around on the spreadsheet > but A1-CF only change if we change the value in B1. I did not say that the result in cell A1 depends on the cursor position. The shown formula depends on the position. The formula is NOT!! =B1 instead the formula is: [value one cell to the right]. Since conditional formats are not limited to one cell the only sane way to work with them in dialogs is to the show the formula based on the current cursor position. Entering the shown formula in the dialog would result in exactly the same behavior as entering your =A1 when you defined the formula. This is just part of how relative references work. > > Even worse, if it was not possible to use so the relative formulas in CF, then > would be impossible in many cases copy CF between cells, because always remains > with the formula of source cell(s). > *Actually Copy doesn't copy the CF. > As I tried to explain this is only the way how formulas with conditional formulas are displayed.
(In reply to comment #6) > (In reply to comment #5) > > Sorry Markus it was created as "enhancement", and I have no interest in > > maintaining a discussion with you to nowhere. So if you I am wrong or you don't > > like to do, no matter, but please return their status, to allow someone else > > take a look. > > Sorry, but this is not a bug. This bug should not be reopened and there will be > nobody else looking at this bug report. If you still feel that you're right and > know calc better than me I think wrong way, this is not about who knows more about calc. I have not called into question your knowledge, so please do not do it with mine. Developers must learn that is very important the feedback from users, at the end, the purpose of the application it is give the best to the user. Not only in programming in many areas of the life happens the same. The experts have too much knowledge to see the things from user side. This is about how a developer and an user understand, what must display and edit the Manager window in Conditional Formatting. > write to the qa list and complain about me closing > this bug. of course I will, in couple of days. > > > > > Sorry again but to me what you explain is not how it works. > > > > In A1 cell, goto to CF introduce in the condition B1, select an style to apply. > > Enter 1 in B1, the CF condition is always true, and do not depend on the actual > > cell where we are, only on the B1 value, we can go around on the spreadsheet > > but A1-CF only change if we change the value in B1. > > I did not say that the result in cell A1 depends on the cursor position. The > shown formula depends on the position. The formula is NOT!! =B1 instead the > formula is: [value one cell to the right]. Right, it is a relative address. > Since conditional formats are not > limited to one cell the only sane way to work with them in dialogs is to the > show the formula based on the current cursor position. This is the basis of the discussion, as user, that can only take me to an error. What is the point to take the current position?. > Entering the shown > formula in the dialog would result in exactly the same behavior as entering > your =A1 when you defined the formula. This is just part of how relative > references work. > > > > > Even worse, if it was not possible to use so the relative formulas in CF, then > > would be impossible in many cases copy CF between cells, because always remains > > with the formula of source cell(s). > > *Actually Copy doesn't copy the CF. > > > > As I tried to explain this is only the way how formulas with conditional > formulas are displayed. Then better do not display it. In summary, the actual displaying and edit in the Manage window only can lead the users to error and confusion. What I want as user is to see the entered formula, and when edit, edit the entered formula. Regards
(In reply to comment #7) > (In reply to comment #6) > > (In reply to comment #5) > > > Sorry Markus it was created as "enhancement", and I have no interest in > > > maintaining a discussion with you to nowhere. So if you I am wrong or you don't > > > like to do, no matter, but please return their status, to allow someone else > > > take a look. > > > > Sorry, but this is not a bug. This bug should not be reopened and there will be > > nobody else looking at this bug report. If you still feel that you're right and > > know calc better than me > > I think wrong way, this is not about who knows more about calc. I have not > called into question your knowledge, so please do not do it with mine. > > Developers must learn that is very important the feedback from users, at the > end, the purpose of the application it is give the best to the user. Not only > in programming in many areas of the life happens the same. The experts have too > much knowledge to see the things from user side. > > This is about how a developer and an user understand, what must display and > edit the Manager window in Conditional Formatting. I understand that relative references in conditional formats are a complex feature. But please also understand that I get from 10 people 12 different suggestions how to make it better. In the end I have to evaluate each suggestion and check how it fits into the bigger picture and if it make sense. I know that this is difficult to understand as user but in the end I have to find a compromise between all the possibilities. For you these behavior might be confusing but for a lot of other users it is how they expect it. > > > write to the qa list and complain about me closing > > this bug. > > of course I will, in couple of days. > > > > > > > > > Sorry again but to me what you explain is not how it works. > > > > > > In A1 cell, goto to CF introduce in the condition B1, select an style to apply. > > > Enter 1 in B1, the CF condition is always true, and do not depend on the actual > > > cell where we are, only on the B1 value, we can go around on the spreadsheet > > > but A1-CF only change if we change the value in B1. > > > > I did not say that the result in cell A1 depends on the cursor position. The > > shown formula depends on the position. The formula is NOT!! =B1 instead the > > formula is: [value one cell to the right]. > > Right, it is a relative address. > > > Since conditional formats are not > > limited to one cell the only sane way to work with them in dialogs is to the > > show the formula based on the current cursor position. > > This is the basis of the discussion, as user, that can only take me to an > error. > > What is the point to take the current position?. Using the current position is the only way to get the same formula as you originally had. Using a different position would result in more irritated users because they would then enter the shown formula string and would get a different result > > > Entering the shown > > formula in the dialog would result in exactly the same behavior as entering > > your =A1 when you defined the formula. This is just part of how relative > > references work. > > > > > > > > Even worse, if it was not possible to use so the relative formulas in CF, then > > > would be impossible in many cases copy CF between cells, because always remains > > > with the formula of source cell(s). > > > *Actually Copy doesn't copy the CF. > > > > > > > As I tried to explain this is only the way how formulas with conditional > > formulas are displayed. > > Then better do not display it. That is even worse. I will not even discuss this. > > In summary, the actual displaying and edit in the Manage window only can lead > the users to error and confusion. > > What I want as user is to see the entered formula, and when edit, edit the > entered formula. That is what I tried to explain you for some time. The entered formula is always relative to a position. I attached a test document that shows the problem that I think you don't understand right now. In the attached document you'll find two conditional formats both are defined as ISNUMBER(C5) or ISNUMBER(F5), so if both conditional formats would be at the same place they would have the same formula with your suggestion. BUT they did have different base cells when they were defined so they show totally different results. For the conditional format in column B it was B4 and for the one in column E it was E7. Now with the current solution you can see exactly that these two formulas are actually not the same and it is the only way to see what the formula really means. ISNUMBER(F5) or ISNUMBER(C5) is not the "real" formula, it is much more and we can make this more only visible to the user with the current solution. I don't want to be rude but at some point a developer that has the overview about all parts of a feature has to decide if a feature request/bug report that has passed QA can and should be implemented. So in this case my call is that the current solution is the only sane one and I doubt that you can convince Eike or Kohei to disagree on this one.
Created attachment 67218 [details] file
Created attachment 67219 [details] Test screenshot. Take a look.
The last screenshot. Win7x64, Version 3.7.0.0.alpha0+ (Build ID: f49db87)
Yeap, this expected. A1 up 3 rows is outside of the valid region. It is even worse if you have this inside a conditional format.
Hi, Reopening because I do a proposal. I do my tests with LO 3.6.3.0+ I think that the problem, from the point of view of the end-user, is that he does not know how to interpret the formula for the simple reason he don't know which is the current cell. Indeed he opens the CF manager to see what are all CF defined in the current sheet, whatever the current cell is. And when he hits the Edit button, he sees a formula which is not the formula he entered. Which is confusing. I think that there is only one step missing when you hit the Edit button in the CF manage: before opening the Edit dialog, the range from which the CF is to be edited, should be select and the current cell moved to the first cell of the range. Then you can edit CF from the CF manage dialog in the same way you do from the menu Format > CF > CF For example, in the Markus's file: the current cell when I open the file is C13. Menu Format > CF > Manage -> clic on B4:B7 then on Edit button. At this moment the current cell should be moved in B4 and the range B4:B7 should be selected. So, without changing something else, you see the formula you had defined for the CF in B4:B7. Best regards. JBF
This is not possible. I will now just remove myself from the bug report. Please never add me to add a bug report again.
(In reply to comment #14) > This is not possible. I will now just remove myself from the bug report. Please > never add me to add a bug report again. Sorry but I cannot remember to have added you to any cc list. Any way, good job. Regards
(In reply to comment #15) > (In reply to comment #14) > > This is not possible. I will now just remove myself from the bug report. Please > > never add me to add a bug report again. > > Sorry but I cannot remember to have added you to any cc list. > Any way, good job. > Regards This was to all QA people. I will again switch to feature work and let the bugs fix themself. It seems the opinion of the developer working on a feature does not count any more so please just ignore me when it comes to bug reports.
Hi Kohei, Please, can you help here? 1/ Do you agree with Markus position about showing the CF of a range relatively to the current cell, what ever this cell is? 2/ Is my proposal in comment #13 really not possible to implement? Discussion on QA ML: http://nabble.documentfoundation.org/Libreoffice-qa-Ask-for-help-on-discrepancy-about-a-enhancement-request-in-bugzilla-tp4008683.html Best regards. JBF
Hello 1. Format> Conditional formating> Manage has to to calculate and show relative references *only* when the current cell is within the range that is associated with a conditional formatting. It has no meaning in other cases (when current cell not within the range). 2. Anyway, It lacks one essential information for understanding the formulas: the reference cell, "base-cell-address" (xml) or "SourcePosition" (property). Markus file is a well-chosen: base-cell-address="Sheet1.B4" for Sheet1.B4:Sheet1.B7 base-cell-address="Sheet1.E7 for Sheet1.E4:Sheet1.E7 I do not like the idea of changing the current selection (as it could be confusing for the user). Limit calculate relative references to concerned cells and display / choose the cell reference would it solve the problem? Regards Pierre-Yves
having a look at the area (with Markus' help).
We (tentatively) have a beautiful plan to improve this for 3.7 - possibly we can put some band-aid in place for 3.6 that improves things for relative reference rendering. As with everything development is limited by both developer bandwidth, and tightness of the test/feedback cycle - which makes every change much more expensive later in time.
Michael Meeks committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=985e7e4f5017b67f2734b8885d81e32e2011e470 fdo#54940 - make editing relative refs more intuitive to me The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Testing vs. master much appreciated; clearly work is ongoing here for 3.7 - if there are no violent complaints - this could be considered for 3.6. Testing should include loading, editing, saving & re-loading complex legacy document to ensure continued function etc. Thanks ! :-)
When the master becomes available, I'll try. Thank you all. Nice to see this improvement incorporated.
Hi Michael, I like these patches, now manage dialog shows CF they are defined in the first cell of the block on which the apply. That's fine from end-user point of view. Now the bug: it seems that defining a CF on a block of cells does not work well: the propagation of the formula on each cell is not done. Step to reproduce: - select A1:A10 - Format > CF > CF - chose "Formula is" and type a formula like "A1 > 0" - validate and close CF dialog - select A5 - Format > CF > CF ==> formula is A1 > 0 instead of A5 > 0 It seems there is some interference between defining and showing CF I did my tests with (Build ID: 78a102e) under Ubuntu 12.04.
Hi Jean-Baptiste first thanks. By now I was unable to download the master with the pacht. > Now the bug: it seems that defining a CF on a block of cells does not work > well: the propagation of the formula on each cell is not done. Step to > reproduce: ..... > - Format > CF > CF > ==> formula is A1 > 0 instead of A5 > 0 I think a solution with a plus in the simplification of the UI, could be: - Eliminate "Format > CF > CF" window. - In "Format > CF > M" window insert the [Add] boton. This would avoid, but mistake, edit only one cell from a range with CF, in other case, you need first go trough the [Add] option for this particular cell.
(In reply to comment #24) > I like these patches, now manage dialog shows CF they are defined in the > first cell of the block on which the apply. That's fine from end-user point > of view. Personally, I would have appreciated a thank-you; just a suggestion; one of those little things that makes life flow -much- more sweetly. > Now the bug: it seems that defining a CF on a block of cells does not work > well: the propagation of the formula on each cell is not done. Step to > reproduce: The relative reference is expressed consistently with reference to the top left hand corner of the range you're currently inside - at least with regard to the CF dialog launched from the Manage range dialog. The idea of a special case for when a cell is selected inside the range is (IMHO) not at all intuitive there. With regard to the standard conditional format dialog though as Mario says the way it currently behaves is really not ideal - it that looks very bug prone to me - with people tempted to re-format single cells inside existing ranges in odd ways that are hard to see etc. Fixing that with an add button is (IIRC) in mind for 3.7 but I suspect will not happen for 3.6. On the other hand, restoring the flow you mention to that way might work - if you play with some hacking around a partial reversion of commit: 9edb6ba992ad2ef092996903d775df5313613bee you might have some luck and come up with a patch. But please - make my life easier by asking for changes more pleasantly. Thanks !
(In reply to comment #26) > (In reply to comment #24) > > I like these patches, now manage dialog shows CF they are defined in the > > first cell of the block on which the apply. That's fine from end-user point > > of view. > > Personally, I would have appreciated a thank-you; just a suggestion; one of > those little things that makes life flow -much- more sweetly. Oh, sorry, I see I did not choose the right words to express how I am happy you decided to investigate this issue. Thank you very much. English is not my native language, so, often, I have difficulties to express properly what I think. So, please, accept my apologies. Best regards. JBF
Hi Michael, Miguel Angel, Markus, I did several tests after having reverted locally (in my own build) the commit 9edb6ba992ad2ef092996903d775df5313613bee as suggested by Michael. Precisely, I reverted this commit after this one : f4b412ebd57016823dde50c5d41bdc39337b6b74 I did my tests accordingly to the following workflow: 1/ use Format > CF > CF to add new CF or modify existing CF 2/ use Format > CF > Manage to have a global view of existing CF and, if needed, choose one to be modified. My results: - Format > CF > CF works as expected as long as you define CF for non overlapping ranges - Format > CF > Manage shows the ranges with CF as they have been defined in Format > CF > CF: * the formula is shown for the cell in the left corner of the range * if you select a range in the list and hit the Edit button, you get a dialog with title "Conditional Formating for <range>" and the formulas are shown for the cell in the left corner of the range * if you hit the Add button in the Edit dialog, you can add a new condition and if you define this new condition for the cell in the left corner of the range, then the condition is propagated over the range, as expected. So, for me, without the commit 9edb6ba992ad2ef092996903d775df5313613bee, these dialogs work as a standard user would expect :-) So I propose to revert this commit in the master if it does not play any role in other area. I think this change should be backported to 3.6 but I propose to wait for that that we have a daily build of the master that Miguel Angel and other interested peoples can test. Remark: during my tests I encountered 2 issues, perhaps independent, I do not know (If it is the case I can file new bug reports): #1: the changes done in Format > CF > CF > Edit does not trigger the flag "modified file" #2: sometimes, changes like condition removed, seems not taken into account (view not updated) until you save and reload the file, or go downstream in the current sheet and go back. I am very happy with this change. Thanks a lot for that. I am sure we are on the way to a CF manager far better than we have in previous versions. The Manage dialog is a clear plus for me and, only for it, I wouldn't go back to old version. Even if some UI improvements, like resizable windows and range pickers, are needed (from my point of view), but that's to be discussed elsewhere. Thank you very much for your patience. :-) Best regards. JBF
Hi Jean-Baptiste; thanks for communicating in a way that makes it motivating and encouraging to read your feedback ! :-) that really helps. Markus - any chance we can leave 9edb6ba992ad2ef092996903d775df5313613bee as it was before ? that'd make CF->CF behave differently to CF->Manage but I guess when people do CF->CF they expect the selection to make a difference. Then - hopefully we can get this bug closed :-) I imagine, that's mostly just a matter of cherry-picking 985e7e4f5017b67f2734b8885d81e32e2011e470 to -3-6 and leaving master to evolve ? :-) Thanks !
Michael Meeks committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8e01b2aca4aad3c4f366b3f8d385b3dbe9aaa886 fdo#54940 - fix last small ergonomic issue. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Marking this fixed; the back-port for -3-6 is squashed to a single patch with another couple of fixes and awaiting review on the mailing list.
Michael Meeks committed a patch related to this issue. It has been pushed to "libreoffice-3-6": http://cgit.freedesktop.org/libreoffice/core/commit/?id=cf83153902ec9cd7d15782cbdbf01df9f449fdbc&g=libreoffice-3-6 fdo#54940 - make editing relative refs more intuitive to me It will be available in LibreOffice 3.6.3. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to comment #32) > Michael Meeks committed a patch related to this issue. > It has been pushed to "libreoffice-3-6": > > http://cgit.freedesktop.org/libreoffice/core/commit/ > ?id=cf83153902ec9cd7d15782cbdbf01df9f449fdbc&g=libreoffice-3-6 > > fdo#54940 - make editing relative refs more intuitive to me Great !!! Works well in LO 3.6.3.0+ (Build ID: cf83153) Tested on my own build on Ubuntu 12.04 x86_64. Thank you very much. JBF
Hello (In reply to comment #32) > Michael Meeks committed a patch related to this issue. >... > It will be available in LibreOffice 3.6.3. > ... > Affected users are encouraged to test the fix and report feedback. Tested and approved on windows 7 64bits with Version 3.6.3.0+ (Build ID: 1e73405) Very good work, thank you very much Regards Pierre-Yves