Data | Validity functionality is intended to allow only defined values to be entered into the field. If incorrect value is entered the error message is displayed "Invalid value".
But data validity is not respected if data is copied from some other cell and pasted over the existing data validity definition.
Steps to Reproduce:
1. Click in A1 cell.
2. Data | Validity.
3. On Criteria tab from Allow field select List.
4. In Entries field type in: AAA and click on OK button.
5. Right of A1 cell arrow appears. You can select AAA and you can press <Delete> key to delete value.
6. Still in A1 field try typing some value like BBB and press OK button. Error is returned "Invalid value". This is expected.
7. Click on some other cell and type in BBB. Copy the cell and move to A1 cell and press CTRL+V to paste the value.
Value is copied successfully over data validity definition.
Data-Validity definition should be respected and on paste "Invalid value" error should be returned.
User Profile Reset: Yes
Version: 188.8.131.52 (x64)
Build ID: 2524958677847fb3bb44820e40380acbe820f960
CPU threads: 3; OS: Windows 6.1; UI render: default;
Locale: sl-SI (sl_SI); Calc: group
User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:52.0) Gecko/20100101 Firefox/52.0
Thanks for your report, grofaty.
From my point of view, this behavior is intended as you copy a cell including all format and other cell attributes.
You can avoid to destroy the cell validity with Edit > Paste Unformatted Text instead of Edit > Paste.
@Tomas, thanks for your reply. What I am doing is preparing document for end-users to fill in some data. I know they are capable of using Copy/Paste and so destroying Data|Validity restriction. If this is working as intended, what is the option to prevent copying data into the Data|Validity protected cell?
Today I don't see any possibility to avoid overwriting a cell but I'm no Calc expert. Protecting a sheet and its cells also protects to do anything with the cells, including inserting valid content.
A quick web search doesn't help. Also I couldn't reproduce with MSO Excel 2013 the assertion  that Excel can handle what you want.
What I can imagine in the future is an exception in the "Protect Sheet" dialog  for Validity and similar functions so that the user can can insert content with the Validity selection list in protected cells but nothing else. I don't know if this is possible in LibreOffice or in the ODF specification and if this is a wanted feature enhancement.
@Heiko, Eike - may I ask for your opinion?
I don't see a workaround either.
Use case here is to protect cells while in other situations you may want to copy the validation. My guess of how often both are relevant is 70/30. So I would not copy/paste the validity check by default but provide a checkbox 'Validity check' in paste special. Could also live with the opposite to enable it by default and disable in paste special, which keeps the legacy behavior.
1. The described behaviour is as the 'Validity' always behaved.
2. Since there is no explicit specification (afaik) the behaviour as it always was cannot be proved a bug.
3. To get what the reporter wants would not require a bugfix but an ENHANCEMENT.
4. In fact more than one enhancemnt would be neded. At least:
4.1. Add an option concerning cell protection to an effect as described by 'Inhibit Full Paste'.
4.2. Add an option under 'Validity' to an effect like 'Check Pasted Content for Validity'.
5. All this would not ensure actual data consistency. As soon as any contents of a validity 'List' or a validity 'Cell Range' are changed, newly invalid contents in cells may occur.
The 'Validity' tool as is is a makshift at best. In specific it may mislead designers of spreadsheets to believe they can make things fool-proof based on it. If this is needed urgently the task should be moved to a database application with its security features and rights management.
Either NOTABUG or Importance = "enhancement".
(Discussion about workarounds may better be placed in ask.libreoffice.org or on any forum on open OfficeSoftware.)
To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.
There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.
If you have time, please do the following:
Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/
If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.
Please DO NOT
Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not
appropriate in this case)
If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/
2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword
Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa
Thank you for helping us make LibreOffice even better for everyone!