Description: Let's consider an .ods document which has two sheets: Sheet1 and Sheet2. On Sheet1 there are two cells which are filled-in with number 1 in the first cell and number 2 in the second one. On the Sheet2, I want to validate data of column A using the values 1 and 2 from Sheet1; so, I want to have in column A from Sheet2 only the numbers 1 or 2 from Sheet1. I go in Sheet2, I select column A and then go to Menu -> Data -> Validity -> Criteria -> Allow: Cell range -> select the range from Sheet1 (numbers 1 and number 2) and OK button. Everything works, but after this, if in Sheet1 number 1 is replaced by number 3, on Sheet2 the cells which have been filled-in with number 1, remain with number 1 and they are not changed with number 3 as they was changed in Sheet1. Is this an error? In my opinion it is because it should be the same situation like in A2=A1 and if A1 is changed from value 1 to value 3, the A2 will be changed with value 3, right? Many thanks for your work! Steps to Reproduce: See Description Actual Results: See Description Expected Results: See Description Reproducible: Always User Profile Reset: No Additional Info: See Description
the data validity feature is an "input" feature, working at the time of data input. it does not reference the selected value with the source range. have you tried with excel? i think it will work same way.
With AOO 4.1.6 and Gnumeric, when in sheet exp A1 is set from 1 to 3 then the value change in the Validity option (second sheet) best regards
You are right, also this behavior as "input" future is also present in Excel; However, at the same time this situation is strange because if I will try to change from keyboard the 1 value on Sheet2 with the same number 1 (considering before example), I received an error message "Invalid value" in situation when in that cell is already filled-in number 1, but because in Sheet1 the number 1 was changed before with number 3, the number 1 can't be written in Sheet2 even if it already exists in the same cell. This is without any logic. The right behavior, could be to receive a message where I'm informed that the next cells (A1, A2 ...) from Sheet2, which have the old value inside, will be changed with the new value, or I can choose to cancel the action and to keep in the validity list the value before. Many thanks!
And regarding the situation that Calc tool has to have a compatibility with Excel behavior, I agree, but my proposal don't change this situation, the idea is to add a new future, which could be better than Excel. I'm strongly convinced that LibreOffice if much better than Excel.
@ Xavier Van Wijmeersch In AOO 4.1.6 is the same behavior, so it doesn't work for me :(
It is not an bug, data validity only runs when you click to select, no formula in the cell only a value that doesn't change if you not select a new one. What you want it's introduce a formula addressing the cell with the selection, but it is no possible with data validity. Please take a look to the help. https://help.libreoffice.org/6.1/en-US/text/scalc/guide/validity.html?DbPAR=CALC#bm_id3156442
Many thanks for your time!