Created attachment 173364 [details] Try selecting the fruits under the Selection column - only the first one will work Hi! The Data->Validity feature for cell ranges doesn't seem to work properly. I'm using LibreOffice v6.4.7.2 with build 1:6.4.7-0ubuntu0.20.04. I attached an example what I made with Libreoffice Calc. The spreadsheet works fine with OnlyOffice Desktop Editors and Excel.
The validity was defined "Fruits.A2:A10000" for cell "A2". Obviously the cell validity was copied down to cell "A3" and due to using a relative addressing scheme the validity range turns top "Fruits.A3:A10001" as per general rules and behavior. But is was expected to stay at "Fruits.A2:A10000". This proceeds until there is no value an longer in the validity list (cell "A7" and validity range being "Fruits.A7:A10005"). To achieve the intended behavior you need to use an absolute adrdessing scheme using "$": - Add "Fruits.A$2:A$10000" as validity for "A2" - Copy cell "A2" down to get the correct validity expression for subsequent rows [1] LibreOffice Help - Addresses and References, Absolute and Relative: https://help.libreoffice.org/latest/en-US/text/scalc/guide/relativ_absolut_ref.html?DbPAR=CALC#bm_id3156423 From my perspective not a bug.
Created attachment 173366 [details] Modified Version of Reporters's Testcase Please see the attached modification of the original testcase file, which uses an absolute addressing scheme for the validity range.
@Uwe Auer calc shouldn't extend data sources because it doesn't make any sense. Other spreadsheets don't have this bug. The range was added through selection so maybe the issue is in the selection system.
I tried your sample and that is how it works in other spreadsheet programs. Also, after I select something from the list and switch to another sheet the selection will become available once for cells without validity rules.
Feel free to reopen...
Is it a goal to match the other spreadsheets' functionality, like the range selection and usage mechanism? Should I create another report for the lingering drop-down?
I guess some input from the UX team would be great
(In reply to lcuserx from comment #3) > Calc shouldn't extend data sources because it doesn't make any > sense. Other spreadsheets don't have this bug. You mean we should always use absolute references by default? And other spreadsheet tools behave respectively? Clearly not. And if you select the source per range selector (the button right of the input field) you do get absolute references ($Fruits.$A$2:$A$6) that wont change on copy/paste. Don't see an issue here. What exactly do you suggest to change?
(In reply to Heiko Tietze from comment #8) > (In reply to lcuserx from comment #3) > > Calc shouldn't extend data sources because it doesn't make any > > sense. Other spreadsheets don't have this bug. > > You mean we should always use absolute references by default? And other > spreadsheet tools behave respectively? Clearly not. > > And if you select the source per range selector (the button right of the > input field) you do get absolute references ($Fruits.$A$2:$A$6) that wont > change on copy/paste. Don't see an issue here. > > What exactly do you suggest to change? I say yes for both of the first two questions.
(In reply to lcuserx from comment #9) > (In reply to Heiko Tietze from comment #8) > > You mean we should always use absolute references by default? And other > > spreadsheet tools behave respectively? Clearly not. > ... > I say yes for both of the first two questions. That's a no-go. Relative addresses are common on all spreadsheet tools. And even if some weird program behaves differently, the majority of users would see it as a regression. Please also note that using the dialog's range function does add absolute references for the validity. So it's NAB (since you reopened I don't touch the status).
(In reply to Heiko Tietze from comment #10) > (In reply to lcuserx from comment #9) > > (In reply to Heiko Tietze from comment #8) > > > You mean we should always use absolute references by default? And other > > > spreadsheet tools behave respectively? Clearly not. > > ... > > I say yes for both of the first two questions. > > That's a no-go. Relative addresses are common on all spreadsheet tools. And > even if some weird program behaves differently, the majority of users would > see it as a regression. > > Please also note that using the dialog's range function does add absolute > references for the validity. So it's NAB (since you reopened I don't touch > the status). I opened this ticket because the default behavior is not the same as it is in excel/onlyoffice and produces incorrect results. Whether you want to correct this or not is your choice and won't affect me because I'm not using Calc anymore.